I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database.
My model:
users <n---n> right_holders <n---n> tracks ---n> raw_detections
I have this table raw_detections with more than 100 million records. I want to sum all raw_detections for a specific user in a specific period. This is the query that I am having problem:
SELECT
DATE(raw_detection.created_at),
SUM(1)
FROM `raw_detection`
INNER JOIN `audio_sources` ON `audio_sources`.`id` = `raw_detection`.`audio_source_id`
INNER JOIN `cities` ON `cities`.`id` = `audio_sources`.`city_id`
INNER JOIN `tracks` ON `raw_detection`.`track_id` = `tracks`.`id`
INNER JOIN `track_right_holders` ON `tracks`.`id` = `track_right_holders`.`track_id`
INNER JOIN `right_holders` ON `track_right_holders`.`right_holder_id` = `right_holders`.`id`
INNER JOIN `user_right_holders` ON `right_holders`.`id` = `user_right_holders`.`right_holder_id`
WHERE `raw_detection`.`duplicated` = 0
AND `user_right_holders`.`user_id` = 1
AND (raw_detection.created_at >= '2015-01-18 00:00:00')
AND (raw_detection.created_at <= '2015-02-19 23:59:59')
AND (audio_sources.source_type = 'Radio')
AND (track_right_holders.role = 'Interpreter')
GROUP BY DATE(raw_detection.created_at);
There are some users that have a lot of detections (tens of thousands) per month.
When raw_detection table is MyISAM, this query takes 2-3s to run. When the table is InnoDB, it takes 10-20s to run. My questions are:
1) Why this is happening? 2) How can I improve this performance for both MyISAM and InnoDB
raw_detection schema:
CREATE TABLE `raw_detection` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`score` int(11) NOT NULL,
`track_id` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`audio_source_id` int(11) NOT NULL,
`duplicated` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_raw_detection_on_duplicated_and_created_at` (`duplicated`,`created_at`),
KEY `index_raw_detection_on_created_at` (`created_at`),
KEY `index_raw_detections_audio_source` (`audio_source_id`,`duplicated`,`created_at`),
KEY `index_raw_detection_on_track_id_and_duplicated_and_created_at` (`track_id`,`duplicated`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=126224926 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
UPDATE 1: Output for query explain
1 SIMPLE user_right_holders ref index_user_right_holders_on_user_id,index_user_right_holders_on_right_holder_id index_user_right_holders_on_user_id 5 const 6 Using where; Using temporary; Using filesort
1 SIMPLE right_holders eq_ref PRIMARY PRIMARY 4 playax.user_right_holders.right_holder_id 1 Using index
1 SIMPLE track_right_holders ref index_track_right_holders_on_track_id,index_track_right_holders_on_right_holder_id2 index_track_right_holders_on_right_holder_id2 773 playax.user_right_holders.right_holder_id,const 4 Using index condition; Using where
1 SIMPLE tracks eq_ref PRIMARY PRIMARY 8 playax.track_right_holders.track_id 1 Using where; Using index
1 SIMPLE raw_detection ref index_raw_detection_on_duplicated_and_created_at,index_raw_detection_on_created_at,index_raw_detections_audio_source,index_raw_detection_on_track_id_and_duplicated_and_created_at,raw_detection2_ix index_raw_detection_on_track_id_and_duplicated_and_created_at 5 playax.tracks.id,const 284 Using index condition
1 SIMPLE audio_sources eq_ref PRIMARY,index_audio_sources_on_city_id,audio_source_city_id,audio_sources_source_type_ix PRIMARY 4 playax.raw_detection.audio_source_id 1 Using where
1 SIMPLE cities eq_ref PRIMARY PRIMARY 4 playax.audio_sources.city_id 1 Using index
UPDATE 2: My Innodb and MyISAM settings:
ignore_builtin_innodb OFF
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_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
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 1073741824
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_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_disable_sort_file_cache OFF
innodb_doublewrite ON
innodb_empty_free_list_algorithm backoff
innodb_fake_changes OFF
innodb_fast_shutdown 1
innodb_file_format Antelope
innodb_file_format_check ON
innodb_file_format_max Antelope
innodb_file_per_table ON
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit 2
innodb_flush_method O_DIRECT
innodb_flush_neighbors 1
innodb_flushing_avg_loops 30
innodb_force_load_corrupted 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_io_capacity 5000
innodb_io_capacity_max 10000
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 8388608
innodb_log_checksum_algorithm innodb
innodb_log_compressed_pages ON
innodb_log_file_size 268435456
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_old_blocks_pct 37
innodb_old_blocks_time 1000
innodb_online_alter_log_max_size 134217728
innodb_open_files 4096
innodb_optimize_fulltext_only OFF
innodb_page_size 16384
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_sched_priority_cleaner 19
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 OFF
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_native_aio ON
innodb_use_stacktrace OFF
innodb_use_sys_malloc ON
innodb_version 5.6.22-71.0
innodb_write_io_threads 4
myisam_block_size 1024
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_mmap_size 18446744073709551615
myisam_recover_options BACKUP
myisam_repair_threads 1
myisam_sort_buffer_size 536870912
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
