Below is the configuration of my DB server:
Apache version : Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8 PHP version : 5.3.13 MySQL version : mysqlnd 5.1.11 Windows Server 2008 Standard Edition Service Pack 2 Compiler: MSVC9 (Visual C++ 2008) Architecture: x86 8x Intel(R) Xeon(R) CPU X5460 @3.16GHz, 8.0GB RAM, Windows Server 2008 Standard Edition Service Pack 2.
In my system, there are few slow queries. When I explain the query, I found that query is not using another index instead the index which is created on the table used for joining. If I tried to add "use index(index_name)", it left that index too. The column datatype is datetime. Is this the reason why query is not using the index? Any suggestion... Is there any MySQL configuration variable responsible for this. I have below coonfiguration:
Sl.# Parameter Value 1. Join_buffer_size 128KB 2. tmp_table_size 128KB 3. max_heap_table_size 128KB 4. innodb_buffer_pool_size 4M 5. Key Buffer 547M 6. Sort Buffer Size 256K 7. Query cache limit 4M 8. Cache size 350M 9. Long query time 5 10. Interactive timeout 300 11. Max Connection 800 12. Thread cache size 36
Below is the result of explain:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra "1","PRIMARY","a","ALL",\N,\N,\N,\N,"35859","Using where; Using filesort" "1","PRIMARY","u","eq_ref","PRIMARY","PRIMARY","4","jprod.a.created_by","1","" "1","PRIMARY","g","eq_ref","PRIMARY","PRIMARY","1","jprod.a.access","1","" "10","DEPENDENT SUBQUERY","jos_tag_rules_content_exclude","eq_ref","PRIMARY\,IDX_ENTITY_ID\,idx_rule_id_jos_tag_rules_content_exclude","PRIMARY","8","jprod.a.id\,const","1","Using where; Using index" "9","DEPENDENT SUBQUERY","inc","index_subquery","PRIMARY\,IDX_ENTITY_ID","PRIMARY","4","func","1","Using index; Using where" "8","DEPENDENT SUBQUERY","jos_tag_rules_content_include","ref","PRIMARY\,IDX_ENTITY_ID\,idx_rule_id_jos_tag_rules_content_include","PRIMARY","4","jprod.a.id","1","Using where; Using index" "7","DEPENDENT SUBQUERY","inc","index_subquery","PRIMARY\,IDX_ENTITY_ID","PRIMARY","4","func","1","Using index; Using where" "5","DEPENDENT SUBQUERY","exc","ref","PRIMARY\,IDX_ENTITY_ID","PRIMARY","4","func","1","Using where; Using index" "6","DEPENDENT UNION","inc","ref","PRIMARY\,IDX_ENTITY_ID","PRIMARY","4","func","1","Using where; Using index" \N,"UNION RESULT","","ALL",\N,\N,\N,\N,\N,"" "2","DEPENDENT SUBQUERY","jos_content_tags","unique_subquery","PRIMARY\,idx_content_id_jos_content_tags\,idx_tag_value_id_jos_content_tags","PRIMARY","8","func\,const","1","Using index; Using where" "3","SUBQUERY","jos_tag_rules_values","ref","idx_field_id_jos_tag_rules_values","idx_field_id_jos_tag_rules_values","4","","180","Using where" "4","SUBQUERY","jos_tag_rules_fields","ref","idx_name_jos_tag_rules_fields","idx_name_jos_tag_rules_fields","767","","1","Using where; Using index"
Below is the list of status variables
Status Variable_name Value Gloabl status Variable_name Value Aborted_clients 2350 Aborted_clients 2347 Binlog_cache_use 1494076 Binlog_cache_use 1493386 Bytes_received 180 Bytes_received 14514902897 Bytes_sent 8124 Bytes_sent 73596494244 Com_admin_commands 0 Com_admin_commands 847561 Com_alter_table 0 Com_alter_table 478 Com_begin 0 Com_begin 1009 Com_change_db 1 Com_change_db 8127737 Com_commit 0 Com_commit 1004 Com_create_table 0 Com_create_table 235 Com_create_trigger 0 Com_create_trigger 25 Com_create_view 0 Com_create_view 4 Com_delete 0 Com_delete 417012 Com_delete_multi 0 Com_delete_multi 218 Com_drop_table 0 Com_drop_table 239 Com_drop_view 0 Com_drop_view 8 Com_insert 0 Com_insert 109750 Com_insert_select 0 Com_insert_select 14 Com_kill 0 Com_kill 104 Com_lock_tables 0 Com_lock_tables 233 Com_optimize 0 Com_optimize 1 Com_rollback 0 Com_rollback 4 Com_select 0 Com_select 4955791 Com_set_option 1 Com_set_option 9473284 Com_show_collations 0 Com_show_collations 3109 Com_show_create_table 0 Com_show_create_table 1181 Com_show_create_trigger 0 Com_show_create_trigger 25 Com_show_databases 0 Com_show_databases 5 Com_show_engine_status 0 Com_show_engine_status 9538 Com_show_fields 0 Com_show_fields 516 Com_show_function_status 0 Com_show_function_status 4 Com_show_grants 0 Com_show_grants 4 Com_show_keys 0 Com_show_keys 60 Com_show_open_tables 1 Com_show_open_tables 26714 Com_show_plugins 0 Com_show_plugins 2 Com_show_procedure_status 0 Com_show_procedure_status 4 Com_show_processlist 0 Com_show_processlist 27338 Com_show_status 2 Com_show_status 16508 Com_show_table_status 0 Com_show_table_status 940 Com_show_tables 0 Com_show_tables 7 Com_show_triggers 0 Com_show_triggers 235 Com_show_variables 0 Com_show_variables 3135 Com_stmt_close 0 Com_stmt_close 279 Com_stmt_execute 0 Com_stmt_execute 284 Com_stmt_prepare 0 Com_stmt_prepare 284 Com_unlock_tables 0 Com_unlock_tables 233 Com_update 0 Com_update 967361 Com_update_multi 0 Com_update_multi 2272 Connections 8132537 Connections 8129927 Created_tmp_disk_tables 0 Created_tmp_disk_tables 349226 Created_tmp_files 63684 Created_tmp_files 63645 Created_tmp_tables 1 Created_tmp_tables 3893159 Handler_commit 0 Handler_commit 7480404 Handler_delete 0 Handler_delete 75596 Handler_prepare 0 Handler_prepare 2151290 Handler_read_first 0 Handler_read_first 29815473 Handler_read_key 0 Handler_read_key 1217425496 Handler_read_next 0 Handler_read_next 3826008161 Handler_read_prev 0 Handler_read_prev 2418519 Handler_read_rnd 0 Handler_read_rnd 9075133 Handler_read_rnd_next 81 Handler_read_rnd_next 3683107601 Handler_rollback 0 Handler_rollback 21610 Handler_update 0 Handler_update 17784530 Handler_write 80 Handler_write 591666282 Innodb_buffer_pool_pages_dirty 58 Innodb_buffer_pool_pages_dirty 67 Innodb_buffer_pool_pages_flushed 1161267 Innodb_buffer_pool_pages_flushed 1160463 Innodb_buffer_pool_read_requests 2863173268 Innodb_buffer_pool_read_requests 2855722562 Innodb_buffer_pool_write_requests 47910334 Innodb_buffer_pool_write_requests 47904489 Innodb_data_fsyncs 284362 Innodb_data_fsyncs 284205 Innodb_data_writes 3562289 Innodb_data_writes 3560356 Innodb_data_written 3802343424 Innodb_data_written 3774550528 Innodb_dblwr_pages_written 1161267 Innodb_dblwr_pages_written 1160463 Innodb_dblwr_writes 38015 Innodb_dblwr_writes 37994 Innodb_log_write_requests 7780429 Innodb_log_write_requests 7778131 Innodb_log_writes 2336822 Innodb_log_writes 2335731 Innodb_os_log_fsyncs 208319 Innodb_os_log_fsyncs 208204 Innodb_os_log_written 98765824 Innodb_os_log_written 97323008 Innodb_pages_written 1161267 Innodb_pages_written 1160463 Innodb_row_lock_time 137380 Innodb_row_lock_time 137349 Innodb_row_lock_waits 5392 Innodb_row_lock_waits 5386 Innodb_rows_deleted 75654 Innodb_rows_deleted 75596 Innodb_rows_inserted 7773757 Innodb_rows_inserted 7773709 Innodb_rows_read 3816403633 Innodb_rows_read 3808082321 Innodb_rows_updated 916125 Innodb_rows_updated 915711 Key_blocks_unused 422872 Key_blocks_unused 422915 Key_read_requests 320523319 Key_read_requests 320497057 Key_reads 6024194 Key_reads 6024151 Key_write_requests 7668445 Key_write_requests 7668386 Last_query_cost 10.499 Last_query_cost 0 Open_tables 194 Open_tables 193 Opened_files 1628563 Opened_files 1628148 Opened_table_definitions 0 Opened_table_definitions 5639 Opened_tables 0 Opened_tables 490561 Qcache_free_blocks 58892 Qcache_free_blocks 60662 Qcache_free_memory 240024696 Qcache_free_memory 245087632 Qcache_hits 30351231 Qcache_hits 30338589 Qcache_inserts 3605606 Qcache_inserts 3602774 Qcache_not_cached 1339432 Qcache_not_cached 1338776 Qcache_queries_in_cache 77058 Qcache_queries_in_cache 75273 Qcache_total_blocks 213544 Qcache_total_blocks 211643 Queries 62827552 Queries 62802229 Questions 5 Questions 62659646 Select_full_join 0 Select_full_join 63072 Select_full_range_join 0 Select_full_range_join 31 Select_range 0 Select_range 378850 Select_scan 1 Select_scan 2750215 Slow_queries 0 Slow_queries 26778 Sort_merge_passes 0 Sort_merge_passes 33570 Sort_range 0 Sort_range 366065 Sort_rows 0 Sort_rows 8796695 Sort_scan 0 Sort_scan 449489 Table_locks_immediate 19683336 Table_locks_immediate 19666285 Threads_cached 196 Threads_cached 193 Threads_connected 7 Threads_connected 10 Uptime 205696 Uptime 205606 Uptime_since_flush_status 205696 Uptime_since_flush_status 205606
