Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9420

order by PK uses PK rather than the more selective secondary key

    XMLWordPrintable

Details

    • 10.1.11

    Description

      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.

      The query to create the problem;

      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`

      My my.ini; (on Windows machine but same problem with Linux)

      [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

      My show variables; (on Windows machine but same problem with Linux)

      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

      I dumped, re-import, run optimize and analyze.... same result.

      Thank you,

      Philippe Jean

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              philippejean Philippe Jean
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.