[#MDEV-9420] order by PK uses PK rather than the more selective secondary key

I have a lot of tables that the query optimizer takes occasionally very bad decisions, full table scan. I only have this problem with version 10.1, no such problem with version 10.0.

You can download a dump file of one of the tables (the smallest of my database) with this problem at http://app.vsmcentrix.com/point_activite.zip. This table have index and the query normaly use one of them. For no reason, the optimizer don't use any index.

select * from `Point_Activite` WHERE `Point_Activite`.`id_activity_list` = 1479 AND `Point_Activite`.`id_contact_Script` = 2075347 AND `Point_Activite`.`id_contact_campaign_pass` = 1920183 ORDER BY `Point_Activite`.`IDPoint_Activite`

[mysqld]

datadir=C:/Program Files/MariaDB 10.1/data

port=3306

skip-name-resolve

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

default_storage_engine=innodb

innodb_buffer_pool_size=2037M

innodb_log_file_size=50M

innodb_file_format=Barracuda

innodb_file_per_table=1

innodb_strict_mode=1

max_allowed_packet=1G

character-set-server=utf8

collation-server=utf8_unicode_ci

transaction-isolation=READ-COMMITTED

[client]

port=3306

aria_block_size	8192

aria_checkpoint_interval	30

aria_checkpoint_log_activity	1048576

aria_encrypt_tables	OFF

aria_force_start_after_recovery_failures	0

aria_group_commit	none

aria_group_commit_interval	0

aria_log_file_size	1073741824

aria_log_purge_type	immediate

aria_max_sort_file_size	9223372036853727232

aria_page_checksum	ON

aria_pagecache_age_threshold	300

aria_pagecache_buffer_size	134217728

aria_pagecache_division_limit	100

aria_pagecache_file_hash_size	512

aria_recover	NORMAL

aria_repair_threads	1

aria_sort_buffer_size	268434432

aria_stats_method	nulls_unequal

aria_sync_log_dir	NEWFILE

aria_used_for_temp_tables	ON

auto_increment_increment	1

auto_increment_offset	1

autocommit	ON

automatic_sp_privileges	ON

back_log	80

basedir	C:\Program Files\MariaDB 10.1\

big_tables	OFF

binlog_annotate_row_events	OFF

binlog_cache_size	32768

binlog_checksum	NONE

binlog_commit_wait_count	0

binlog_commit_wait_usec	100000

binlog_direct_non_transactional_updates	OFF

binlog_format	STATEMENT

binlog_optimize_thread_scheduling	ON

binlog_row_image	FULL

binlog_stmt_cache_size	32768

bulk_insert_buffer_size	8388608

character_set_client	utf8mb4

character_set_connection	utf8mb4

character_set_database	utf8

character_set_filesystem	binary

character_set_results	utf8mb4

character_set_server	utf8

character_set_system	utf8

character_sets_dir	C:\Program Files\MariaDB 10.1\share\charsets\

collation_connection	utf8mb4_general_ci

collation_database	utf8_unicode_ci

collation_server	utf8_unicode_ci

completion_type	NO_CHAIN

concurrent_insert	AUTO

connect_timeout	10

datadir	C:\Program Files\MariaDB 10.1\data\

date_format	%Y-%m-%d

datetime_format	%Y-%m-%d %H:%i:%s

deadlock_search_depth_long	15

deadlock_search_depth_short	4

deadlock_timeout_long	50000000

deadlock_timeout_short	10000

debug_no_thread_alarm	OFF

default_master_connection

default_regex_flags

default_storage_engine	InnoDB

default_tmp_storage_engine

default_week_format	0

delay_key_write	ON

delayed_insert_limit	100

delayed_insert_timeout	300

delayed_queue_size	1000

div_precision_increment	4

encrypt_binlog	OFF

encrypt_tmp_disk_tables	OFF

encrypt_tmp_files	OFF

enforce_storage_engine

error_count	0

event_scheduler	OFF

expensive_subquery_limit	100

expire_logs_days	0

explicit_defaults_for_timestamp	OFF

external_user

extra_max_connections	1

extra_port	0

flush	OFF

flush_time	0

foreign_key_checks	ON

ft_boolean_syntax	+ -><()~*:""&|

ft_max_word_len	84

ft_min_word_len	4

ft_query_expansion_limit	20

ft_stopword_file	(built-in)

general_log	OFF

general_log_file	VSMPOR20.log

group_concat_max_len	1024

gtid_binlog_pos

gtid_binlog_state

gtid_current_pos

gtid_domain_id	0

gtid_ignore_duplicates	OFF

gtid_seq_no	0

gtid_slave_pos

gtid_strict_mode	OFF

have_compress	YES

have_crypt	NO

have_dynamic_loading	YES

have_geometry	YES

have_openssl	NO

have_profiling	YES

have_query_cache	YES

have_rtree_keys	YES

have_ssl	DISABLED

have_symlink	NO

histogram_size	0

histogram_type	SINGLE_PREC_HB

host_cache_size	279

hostname	VSMPOR20

identity	0

ignore_builtin_innodb	OFF

ignore_db_dirs

in_transaction	0

init_connect

init_file

init_slave

innodb_adaptive_flushing	ON

innodb_adaptive_flushing_lwm	10.000000

innodb_adaptive_hash_index	ON

innodb_adaptive_hash_index_partitions	1

innodb_adaptive_max_sleep_delay	150000

innodb_additional_mem_pool_size	8388608

innodb_api_bk_commit_interval	5

innodb_api_disable_rowlock	OFF

innodb_api_enable_binlog	OFF

innodb_api_enable_mdl	OFF

innodb_api_trx_level	0

innodb_autoextend_increment	64

innodb_autoinc_lock_mode	1

innodb_background_scrub_data_check_interval	3600

innodb_background_scrub_data_compressed	OFF

innodb_background_scrub_data_interval	604800

innodb_background_scrub_data_uncompressed	OFF

innodb_buf_dump_status_frequency	0

innodb_buffer_pool_dump_at_shutdown	OFF

innodb_buffer_pool_dump_now	OFF

innodb_buffer_pool_dump_pct	100

innodb_buffer_pool_filename	ib_buffer_pool

innodb_buffer_pool_instances	8

innodb_buffer_pool_load_abort	OFF

innodb_buffer_pool_load_at_startup	OFF

innodb_buffer_pool_load_now	OFF

innodb_buffer_pool_populate	OFF

innodb_buffer_pool_size	2135949312

innodb_change_buffer_max_size	25

innodb_change_buffering	all

innodb_checksum_algorithm	INNODB

innodb_checksums	ON

innodb_cleaner_lsn_age_factor	HIGH_CHECKPOINT

innodb_cmp_per_index_enabled	OFF

innodb_commit_concurrency	0

innodb_compression_algorithm	none

innodb_compression_failure_threshold_pct	5

innodb_compression_level	6

innodb_compression_pad_pct_max	50

innodb_concurrency_tickets	5000

innodb_corrupt_table_action	assert

innodb_data_file_path	ibdata1:12M:autoextend

innodb_data_home_dir

innodb_default_encryption_key_id	1

innodb_defragment	ON

innodb_defragment_fill_factor	0.900000

innodb_defragment_fill_factor_n_recs	20

innodb_defragment_frequency	40

innodb_defragment_n_pages	7

innodb_defragment_stats_accuracy	0

innodb_disable_sort_file_cache	OFF

innodb_doublewrite	ON

innodb_empty_free_list_algorithm	BACKOFF

innodb_encrypt_log	OFF

innodb_encrypt_tables	OFF

innodb_encryption_rotate_key_age	1

innodb_encryption_rotation_iops	100

innodb_encryption_threads	0

innodb_fake_changes	OFF

innodb_fast_shutdown	1

innodb_fatal_semaphore_wait_threshold	600

innodb_file_format	Barracuda

innodb_file_format_check	ON

innodb_file_format_max	Barracuda

innodb_file_per_table	ON

innodb_flush_log_at_timeout	1

innodb_flush_log_at_trx_commit	1

innodb_flush_method

innodb_flush_neighbors	1

innodb_flushing_avg_loops	30

innodb_force_load_corrupted	OFF

innodb_force_primary_key	OFF

innodb_force_recovery	0

innodb_foreground_preflush	EXPONENTIAL_BACKOFF

innodb_ft_aux_table

innodb_ft_cache_size	8000000

innodb_ft_enable_diag_print	OFF

innodb_ft_enable_stopword	ON

innodb_ft_max_token_size	84

innodb_ft_min_token_size	3

innodb_ft_num_word_optimize	2000

innodb_ft_result_cache_limit	2000000000

innodb_ft_server_stopword_table

innodb_ft_sort_pll_degree	2

innodb_ft_total_cache_size	640000000

innodb_ft_user_stopword_table

innodb_idle_flush_pct	100

innodb_immediate_scrub_data_uncompressed	OFF

innodb_instrument_semaphores	OFF

innodb_io_capacity	200

innodb_io_capacity_max	2000

innodb_kill_idle_transaction	0

innodb_large_prefix	OFF

innodb_lock_wait_timeout	50

innodb_locking_fake_changes	ON

innodb_locks_unsafe_for_binlog	OFF

innodb_log_arch_dir	.\

innodb_log_arch_expire_sec	0

innodb_log_archive	OFF

innodb_log_block_size	512

innodb_log_buffer_size	16777216

innodb_log_checksum_algorithm	INNODB

innodb_log_compressed_pages	OFF

innodb_log_file_size	52428800

innodb_log_files_in_group	2

innodb_log_group_home_dir	.\

innodb_lru_scan_depth	1024

innodb_max_bitmap_file_size	104857600

innodb_max_changed_pages	1000000

innodb_max_dirty_pages_pct	75.000000

innodb_max_dirty_pages_pct_lwm	0.001000

innodb_max_purge_lag	0

innodb_max_purge_lag_delay	0

innodb_mirrored_log_groups	1

innodb_monitor_disable

innodb_monitor_enable

innodb_monitor_reset

innodb_monitor_reset_all

innodb_mtflush_threads	8

innodb_old_blocks_pct	37

innodb_old_blocks_time	1000

innodb_online_alter_log_max_size	134217728

innodb_open_files	2000

innodb_optimize_fulltext_only	OFF

innodb_page_size	16384

innodb_prefix_index_cluster_optimization	OFF

innodb_print_all_deadlocks	OFF

innodb_purge_batch_size	300

innodb_purge_threads	1

innodb_random_read_ahead	OFF

innodb_read_ahead_threshold	56

innodb_read_io_threads	4

innodb_read_only	OFF

innodb_replication_delay	0

innodb_rollback_on_timeout	OFF

innodb_rollback_segments	128

innodb_scrub_log	OFF

innodb_scrub_log_speed	256

innodb_show_locks_held	10

innodb_show_verbose_locks	0

innodb_simulate_comp_failures	0

innodb_sort_buffer_size	1048576

innodb_spin_wait_delay	6

innodb_stats_auto_recalc	ON

innodb_stats_method	nulls_equal

innodb_stats_modified_counter	0

innodb_stats_on_metadata	OFF

innodb_stats_persistent	ON

innodb_stats_persistent_sample_pages	20

innodb_stats_sample_pages	8

innodb_stats_traditional	ON

innodb_stats_transient_sample_pages	8

innodb_status_output	OFF

innodb_status_output_locks	OFF

innodb_strict_mode	ON

innodb_support_xa	ON

innodb_sync_array_size	1

innodb_sync_spin_loops	30

innodb_table_locks	ON

innodb_thread_concurrency	0

innodb_thread_sleep_delay	10000

innodb_track_changed_pages	OFF

innodb_undo_directory	.

innodb_undo_logs	128

innodb_undo_tablespaces	0

innodb_use_atomic_writes	OFF

innodb_use_fallocate	OFF

innodb_use_global_flush_log_at_trx_commit	ON

innodb_use_mtflush	OFF

innodb_use_native_aio	ON

innodb_use_stacktrace	OFF

innodb_use_sys_malloc	ON

innodb_use_trim	OFF

innodb_version	5.6.26-76.0

innodb_write_io_threads	4

insert_id	0

interactive_timeout	28800

join_buffer_size	262144

join_buffer_space_limit	2097152

join_cache_level	2

keep_files_on_create	OFF

key_buffer_size	134217728

key_cache_age_threshold	300

key_cache_block_size	1024

key_cache_division_limit	100

key_cache_file_hash_size	512

key_cache_segments	0

large_files_support	ON

large_page_size	0

large_pages	OFF

last_gtid

last_insert_id	0

lc_messages	en_US

lc_messages_dir

lc_time_names	en_US

license	GPL

local_infile	ON

lock_wait_timeout	31536000

log_bin	OFF

log_bin_basename

log_bin_index

log_bin_trust_function_creators	OFF

log_error	C:\Program Files\MariaDB 10.1\data\VSMPOR20.err

log_output	FILE

log_queries_not_using_indexes	OFF

log_slave_updates	OFF

log_slow_filter	admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

log_slow_rate_limit	1

log_slow_verbosity

log_tc_size	24576

log_warnings	1

long_query_time	10.000000

low_priority_updates	OFF

lower_case_file_system	ON

lower_case_table_names	1

master_verify_checksum	OFF

max_allowed_packet	1073741824

max_binlog_cache_size	18446744073709547520

max_binlog_size	1073741824

max_binlog_stmt_cache_size	18446744073709547520

max_connect_errors	100

max_connections	151

max_delayed_threads	20

max_digest_length	1024

max_error_count	64

max_heap_table_size	16777216

max_insert_delayed_threads	20

max_join_size	18446744073709551615

max_length_for_sort_data	1024

max_long_data_size	1073741824

max_prepared_stmt_count	16382

max_relay_log_size	1073741824

max_seeks_for_key	4294967295

max_sort_length	1024

max_sp_recursion_depth	0

max_statement_time	0.000000

max_tmp_tables	32

max_user_connections	0

max_write_lock_count	4294967295

metadata_locks_cache_size	1024

metadata_locks_hash_instances	8

min_examined_row_limit	0

mrr_buffer_size	262144

multi_range_count	256

myisam_block_size	1024

myisam_data_pointer_size	6

myisam_max_sort_file_size	2146435072

myisam_mmap_size	18446744073709551615

myisam_recover_options	DEFAULT

myisam_repair_threads	1

myisam_sort_buffer_size	134216704

myisam_stats_method	NULLS_UNEQUAL

myisam_use_mmap	OFF

mysql56_temporal_format	ON

named_pipe	OFF

net_buffer_length	16384

net_read_timeout	30

net_retry_count	10

net_write_timeout	60

old	OFF

old_alter_table	OFF

old_mode

old_passwords	OFF

open_files_limit	6210

optimizer_prune_level	1

optimizer_search_depth	62

optimizer_selectivity_sampling_limit	100

optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

optimizer_use_condition_selectivity	1

performance_schema	OFF

performance_schema_accounts_size	-1

performance_schema_digests_size	-1

performance_schema_events_stages_history_long_size	-1

performance_schema_events_stages_history_size	-1

performance_schema_events_statements_history_long_size	-1

performance_schema_events_statements_history_size	-1

performance_schema_events_waits_history_long_size	-1

performance_schema_events_waits_history_size	-1

performance_schema_hosts_size	-1

performance_schema_max_cond_classes	80

performance_schema_max_cond_instances	-1

performance_schema_max_digest_length	1024

performance_schema_max_file_classes	50

performance_schema_max_file_handles	32768

performance_schema_max_file_instances	-1

performance_schema_max_mutex_classes	200

performance_schema_max_mutex_instances	-1

performance_schema_max_rwlock_classes	40

performance_schema_max_rwlock_instances	-1

performance_schema_max_socket_classes	10

performance_schema_max_socket_instances	-1

performance_schema_max_stage_classes	150

performance_schema_max_statement_classes	178

performance_schema_max_table_handles	-1

performance_schema_max_table_instances	-1

performance_schema_max_thread_classes	50

performance_schema_max_thread_instances	-1

performance_schema_session_connect_attrs_size	-1

performance_schema_setup_actors_size	100

performance_schema_setup_objects_size	100

performance_schema_users_size	-1

pid_file	C:\Program Files\MariaDB 10.1\data\VSMPOR20.pid

plugin_dir	C:\Program Files\MariaDB 10.1\lib\plugin\

plugin_maturity	unknown

port	3306

preload_buffer_size	32768

profiling	OFF

profiling_history_size	15

progress_report_time	5

protocol_version	10

proxy_user

pseudo_slave_mode	OFF

pseudo_thread_id	20

query_alloc_block_size	16384

query_cache_limit	1048576

query_cache_min_res_unit	4096

query_cache_size	1048576

query_cache_strip_comments	OFF

query_cache_type	OFF

query_cache_wlock_invalidate	OFF

query_prealloc_size	24576

rand_seed1	1038885281

rand_seed2	915076684

range_alloc_block_size	4096

read_buffer_size	131072

read_only	OFF

read_rnd_buffer_size	262144

relay_log

relay_log_basename

relay_log_index

relay_log_info_file	relay-log.info

relay_log_purge	ON

relay_log_recovery	OFF

relay_log_space_limit	0

replicate_annotate_row_events	OFF

replicate_do_db

replicate_do_table

replicate_events_marked_for_skip	REPLICATE

replicate_ignore_db

replicate_ignore_table

replicate_wild_do_table

replicate_wild_ignore_table

report_host

report_password

report_port	3306

report_user

rowid_merge_buff_size	8388608

secure_auth	ON

secure_file_priv

server_id	0

shared_memory	OFF

shared_memory_base_name	MYSQL

skip_external_locking	ON

skip_name_resolve	ON

skip_networking	OFF

skip_parallel_replication	OFF

skip_replication	OFF

skip_show_database	OFF

slave_compressed_protocol	OFF

slave_ddl_exec_mode	IDEMPOTENT

slave_domain_parallel_threads	0

slave_exec_mode	STRICT

slave_load_tmpdir	C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp

slave_max_allowed_packet	1073741824

slave_net_timeout	3600

slave_parallel_max_queued	131072

slave_parallel_mode	conservative

slave_parallel_threads	0

slave_run_triggers_for_rbr	NO

slave_skip_errors	OFF

slave_sql_verify_checksum	ON

slave_transaction_retries	10

slave_type_conversions

slow_launch_time	2

slow_query_log	OFF

slow_query_log_file	VSMPOR20-slow.log

socket	MySQL

sort_buffer_size	2097152

sql_auto_is_null	OFF

sql_big_selects	ON

sql_buffer_result	OFF

sql_log_bin	ON

sql_log_off	OFF

sql_mode	STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

sql_notes	ON

sql_quote_show_create	ON

sql_safe_updates	OFF

sql_select_limit	18446744073709551615

sql_slave_skip_counter	0

sql_warnings	OFF

ssl_ca

ssl_capath

ssl_cert

ssl_cipher

ssl_crl

ssl_crlpath

ssl_key

storage_engine	InnoDB

stored_program_cache	256

strict_password_validation	ON

sync_binlog	0

sync_frm	ON

sync_master_info	10000

sync_relay_log	10000

sync_relay_log_info	10000

system_time_zone	Est

table_definition_cache	400

table_open_cache	2000

thread_cache_size	0

thread_concurrency	10

thread_handling	pool-of-threads

thread_pool_max_threads	1000

thread_pool_min_threads	1

thread_stack	294912

time_format	%H:%i:%s

time_zone	SYSTEM

timed_mutexes	OFF

timestamp	1452785129.960605

tmp_table_size	16777216

tmpdir	C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp

transaction_alloc_block_size	8192

transaction_prealloc_size	4096

tx_isolation	READ-COMMITTED

tx_read_only	OFF

unique_checks	ON

updatable_views_with_limit	YES

use_stat_tables	NEVER

userstat	OFF

version	10.1.10-MariaDB

version_comment	mariadb.org binary distribution

version_compile_machine	x64

version_compile_os	Win64

version_malloc_library	system

version_ssl_library	YaSSL 2.3.8

wait_timeout	28800

warning_count	0

MariaDB [test]> explain select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ORDER BY `IDPoint_Activite`;

+------+-------------+----------------+-------+-------------------------------------------------------------+---------+---------+------+---------+-------------+

| id   | select_type | table          | type  | possible_keys                                               | key     | key_len | ref  | rows    | Extra       |

+------+-------------+----------------+-------+-------------------------------------------------------------+---------+---------+------+---------+-------------+

|    1 | SIMPLE      | point_activite | index | id_contact_Script,id_activity_list,id_contact_campaign_pass | PRIMARY | 8       | NULL | 1794955 | Using where |

+------+-------------+----------------+-------+-------------------------------------------------------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> explain select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;+------+-------------+----------------+-------------+-------------------------------------------------------------+--------------------------------------------+---------+------+------+--------------------------------------------------------------------------+

| id   | select_type | table          | type        | possible_keys                                               | key                                        | key_len | ref  | rows | Extra                                                                    |

+------+-------------+----------------+-------------+-------------------------------------------------------------+--------------------------------------------+---------+------+------+--------------------------------------------------------------------------+

|    1 | SIMPLE      | point_activite | index_merge | id_contact_Script,id_activity_list,id_contact_campaign_pass | id_contact_campaign_pass,id_contact_Script | 9,9     | NULL |    1 | Using intersect(id_contact_campaign_pass,id_contact_Script); Using where |

+------+-------------+----------------+-------------+-------------------------------------------------------------+--------------------------------------------+---------+------+------+--------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> ALTER TABLE point_activite ADD INDEX script_activity_campaign_pass ( id_contact_Script, id_activity_list, id_contact_campaign_pass);

Query OK, 0 rows affected, 2 warnings (7.07 sec)

Records: 0  Duplicates: 0  Warnings: 2

MariaDB [test]> show warnings;

+---------+------+--------------------------------------------------------------------+

| Level   | Code | Message                                                            |

+---------+------+--------------------------------------------------------------------+

| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. |

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                               |

| point_activite | CREATE TABLE `point_activite` (

  `IDPoint_Activite` bigint(20) NOT NULL AUTO_INCREMENT,

  `ID_Cscript` bigint(20) DEFAULT '0',

  `titre_FR` longtext COLLATE utf8_unicode_ci,

  `titre_en` longtext COLLATE utf8_unicode_ci,

  `Description_fr` longtext COLLATE utf8_unicode_ci,

  `Description_en` longtext COLLATE utf8_unicode_ci,

  `Points` int(11) DEFAULT '0',

  `id_contact_Script` bigint(20) DEFAULT '0',

  `Date` date DEFAULT NULL,

  `Heure` time DEFAULT NULL,

  `id_Address` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',

  `source` longtext COLLATE utf8_unicode_ci,

  `id_activity_list` bigint(20) DEFAULT '0',

  `Browser` varchar(150) COLLATE utf8_unicode_ci DEFAULT '',

  `verified_by_cakemail` tinyint(1) DEFAULT '0',

  `ID_send_info_html` bigint(20) DEFAULT '0',

  `verified_marketing_automation` tinyint(1) DEFAULT '0',

  `id_contact_campaign_pass` bigint(20) DEFAULT '0',

  PRIMARY KEY (`IDPoint_Activite`),

  KEY `ID_Cscript` (`ID_Cscript`),

  KEY `id_contact_Script` (`id_contact_Script`),

  KEY `Date` (`Date`),

  KEY `id_Address` (`id_Address`),

  KEY `id_activity_list` (`id_activity_list`),

  KEY `Browser` (`Browser`),

  KEY `verified_by_cakemail` (`verified_by_cakemail`),

  KEY `ID_send_info_html` (`ID_send_info_html`),

  KEY `verified_marketing_automation` (`verified_marketing_automation`),

  KEY `id_contact_campaign_pass` (`id_contact_campaign_pass`),

  KEY `script_activity_campaign_pass` (`id_contact_Script`,`id_activity_list`,`id_contact_campaign_pass`)

) ENGINE=InnoDB AUTO_INCREMENT=4115807 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |

MariaDB [test]> explain select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ORDER BY `IDPoint_Activite`;

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

| id   | select_type | table          | type  | possible_keys                                                                             | key     | key_len | ref  | rows    | Extra       |

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

|    1 | SIMPLE      | point_activite | index | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | PRIMARY | 8       | NULL | 1794955 | Using where |

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> explain select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

| id   | select_type | table          | type        | possible_keys                                                                             | key                                                    | key_len | ref  | rows | Extra                                                                                |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

|    1 | SIMPLE      | point_activite | index_merge | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | id_contact_campaign_pass,script_activity_campaign_pass | 9,27    | NULL |    1 | Using intersect(id_contact_campaign_pass,script_activity_campaign_pass); Using where |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

MariaDB [test]> select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

| IDPoint_Activite | ID_Cscript | titre_FR      | titre_en      | Description_fr | Description_en | Points | id_contact_Script | Date       | Heure    | id_Address | source | id_activity_list | Browser | verified_by_cakemail | ID_send_info_html | verified_marketing_automation | id_contact_campaign_pass |

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

|          4112306 |       1483 | Lead Accepted | Lead Accepted |                |                |      0 |           2075347 | 2016-01-13 | 17:49:51 | 10.25.2.40 |        |             1479 |         |                    1 |              3921 |                             0 |                  1920183 |

|          4112312 |       1483 | Lead Accepted | Lead Accepted |                |                |      0 |           2075347 | 2016-01-13 | 17:54:53 | 10.25.2.43 |        |             1479 |         |                    1 |              3921 |                             0 |                  1920183 |

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

2 rows in set (0.00 sec)

MariaDB [test]>  select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ORDER BY `IDPoint_Activite`;

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

| IDPoint_Activite | ID_Cscript | titre_FR      | titre_en      | Description_fr | Description_en | Points | id_contact_Script | Date       | Heure    | id_Address | source | id_activity_list | Browser | verified_by_cakemail | ID_send_info_html | verified_marketing_automation | id_contact_campaign_pass |

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

|          4112306 |       1483 | Lead Accepted | Lead Accepted |                |                |      0 |           2075347 | 2016-01-13 | 17:49:51 | 10.25.2.40 |        |             1479 |         |                    1 |              3921 |                             0 |                  1920183 |

|          4112312 |       1483 | Lead Accepted | Lead Accepted |                |                |      0 |           2075347 | 2016-01-13 | 17:54:53 | 10.25.2.43 |        |             1479 |         |                    1 |              3921 |                             0 |                  1920183 |

+------------------+------------+---------------+---------------+----------------+----------------+--------+-------------------+------------+----------+------------+--------+------------------+---------+----------------------+-------------------+-------------------------------+--------------------------+

2 rows in set (1.57 sec)

| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on |

MariaDB [test]> explain  select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

| id   | select_type | table          | type        | possible_keys                                                                             | key                                                    | key_len | ref  | rows | Extra                                                                                |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

|    1 | SIMPLE      | point_activite | index_merge | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | id_contact_campaign_pass,script_activity_campaign_pass | 9,27    | NULL |    1 | Using intersect(id_contact_campaign_pass,script_activity_campaign_pass); Using where |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> set optimizer_switch='index_merge_sort_intersection=on,engine_condition_pushdown=on,mrr=on,mrr_sort_keys=on';

MariaDB [test]> set join_cache_level=6;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain  select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

| id   | select_type | table          | type        | possible_keys                                                                             | key                                                    | key_len | ref  | rows | Extra                                                                                |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

|    1 | SIMPLE      | point_activite | index_merge | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | id_contact_campaign_pass,script_activity_campaign_pass | 9,27    | NULL |    1 | Using intersect(id_contact_campaign_pass,script_activity_campaign_pass); Using where |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

MariaDB [test]> set use_stat_tables='preferably', histogram_size=255, optimizer_use_condition_selectivity=5; analyze table point_activite persistent for all;

Query OK, 0 rows affected (0.00 sec)

+---------------------+---------+----------+-----------------------------------------+

| Table               | Op      | Msg_type | Msg_text                                |

+---------------------+---------+----------+-----------------------------------------+

| test.point_activite | analyze | status   | Engine-independent statistics collected |

| test.point_activite | analyze | status   | OK                                      |

+---------------------+---------+----------+-----------------------------------------+

MariaDB [test]> explain  select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ;

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

| id   | select_type | table          | type        | possible_keys                                                                             | key                                                    | key_len | ref  | rows | Extra                                                                                |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

|    1 | SIMPLE      | point_activite | index_merge | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | id_contact_campaign_pass,script_activity_campaign_pass | 9,27    | NULL |    1 | Using intersect(id_contact_campaign_pass,script_activity_campaign_pass); Using where |

+------+-------------+----------------+-------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------+------+--------------------------------------------------------------------------------------+

MariaDB [test]> explain  select * from point_activite WHERE `id_activity_list` = 1479 AND `id_contact_Script` = 2075347 AND `id_contact_campaign_pass` = 1920183 ORDER BY `IDPoint_Activite`;

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

| id   | select_type | table          | type  | possible_keys                                                                             | key     | key_len | ref  | rows    | Extra       |

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

|    1 | SIMPLE      | point_activite | index | id_contact_Script,id_activity_list,id_contact_campaign_pass,script_activity_campaign_pass | PRIMARY | 8       | NULL | 2095716 | Using where |

+------+-------------+----------------+-------+-------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

MariaDB [test]> show index from point_activite;

+----------------+------------+-------------------------------+--------------+-------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table          | Non_unique | Key_name                      | Seq_in_index | Column_name                   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------------+------------+-------------------------------+--------------+-------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| point_activite |          0 | PRIMARY                       |            1 | IDPoint_Activite              | A         |     2095716 |     NULL | NULL   |      | BTREE      |         |               |

| point_activite |          1 | ID_Cscript                    |            1 | ID_Cscript                    | A         |         436 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | id_contact_Script             |            1 | id_contact_Script             | A         |      205930 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | Date                          |            1 | Date                          | A         |        1462 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | id_Address                    |            1 | id_Address                    | A         |      130172 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | id_activity_list              |            1 | id_activity_list              | A         |        1392 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | Browser                       |            1 | Browser                       | A         |      245405 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | verified_by_cakemail          |            1 | verified_by_cakemail          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | ID_send_info_html             |            1 | ID_send_info_html             | A         |         933 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | verified_marketing_automation |            1 | verified_marketing_automation | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | id_contact_campaign_pass      |            1 | id_contact_campaign_pass      | A         |      170152 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | script_activity_campaign_pass |            1 | id_contact_Script             | A         |      205930 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | script_activity_campaign_pass |            2 | id_activity_list              | A         |      260976 |     NULL | NULL   | YES  | BTREE      |         |               |

| point_activite |          1 | script_activity_campaign_pass |            3 | id_contact_campaign_pass      | A         |      390518 |     NULL | NULL   | YES  | BTREE      |         |               |

+----------------+------------+-------------------------------+--------------+-------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

14 rows in set (0.00 sec)

MariaDB [test]> select version();

+---------------------+

| version()           |

+---------------------+

| 10.1.10-MariaDB-log |

+---------------------+

1 row in set (0.00 sec)

[MDEV-9420] order by PK uses PK rather than the more selective secondary key Created: 2016-01-14 Updated: 2016-01-26 Resolved: 2016-01-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1.10
Fix Version/s:	10.1.11

[MDEV-9420] order by PK uses PK rather than the more selective secondary key Created: 2016-01-14 Updated: 2016-01-26 Resolved: 2016-01-26