Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
check your table collation if you are joining across varchar fields make sure they are all the same. –  Sam D Dec 9 '13 at 17:48
    
without explain it's difficult to help but look here dba.stackexchange.com/questions/48072/… @RolandoMySQLDBA has a good answer –  Sam D Dec 9 '13 at 17:51
    
I have updated my question with explain result. –  ursitesion Dec 10 '13 at 5:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.