Help with slow select count(*)
I see in your 'Explain's, the 2 queries use different indexes, the fast one uses soc_date_idx, and the slower one uses q_idx. The trick, perhaps is to force soc_date_idx to be used in the 2nd case. (Adding ORDER BY soc_date might do it, 'soc_date=X and (queue_id=Y and server_id=Z) ) might do it. I dunno. Perhaps if you do ANALYZE TABLE, it will comply with your desire. Cheers, Kent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with slow select count(*)
Hi, Can anyone please explain the following? I do two select count(*)'s from a big table, (21,000,000 + rows 6.5 Gb). All columns in the where are indexed. The first select is very quick, the second very slow. The ONLY difference is the value of one column variable (queue_id), which is an INT UNSIGNED; Here is the quick one . mysql select count(server_id) from log where server_id = 1 and queue_id = 1 and soc_date = '2001-02-18'; +--+ | count(*) | +--+ | 1703 | +--+ 1 row in set (2.05 sec) And here is the slow one .. mysql select count(server_id) from log where server_id = 1 and queue_id = 5 and soc_date = '2001-02-18'; +--+ | count(*) | +--+ |5 | +--+ 1 row in set (4 min 9.44 sec) As you can see all columns are indexed: mysql show index from log; +---++---+--+--+ ---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++---+--+--+ ---+-+--++-+ | log | 1 | log_id_idx|1 | log_id | A |NULL | NULL | NULL | NULL| | log | 1 | interface_idx |1 | interface_id | A | 1836325 | NULL | NULL | NULL| | log | 1 | soc_date_idx |1 | soc_date | A | 427 | NULL | NULL | NULL| | log | 1 | server_idx|1 | server_id| A | 12 | NULL | NULL | NULL| | log | 1 | q_idx |1 | queue_id | A | 185 | NULL | NULL | NULL| +---++---+--+--+ ---+-+--++-+ 5 rows in set (0.00 sec) An explain between the two seems to indicate that the query that is quick is trawling through more records than the slow one?! I'm confused as to what the explain is trying to tell me. mysql explain select count(server_id) from log where server_id = 1 and queue_id = 1 and soc_date = '2001-02-18'; +---+--+---+--+-+--- +++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--- +++ | log | ref | soc_date_idx,server_idx,q_idx | soc_date_idx | 4 | const | 337190 | where used | +---+--+---+--+-+--- +++ 1 row in set (0.00 sec) mysql explain select count(server_id) from log where server_id = 1 and queue_id = 5 and soc_date = '2001-02-18'; +---+--+---+---+-+---+-- -++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+-- -++ | log | ref | soc_date_idx,server_idx,q_idx | q_idx | 5 | const | 58437 | where used | +---+--+---+---+-+---+-- -++ 1 row in set (35.08 sec) mysql show variables; |-- |Variable_name | Value |-- | ansi_mode | OFF | back_log| 50 | basedir | /usr/local/mysql/ | bdb_cache_size | 8388600 | bdb_home| /home/mysql/data/ | bdb_logdir | | bdb_tmpdir | /home/mysql/data/tmp/ | character_set | latin1 | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr | | connect_timeout | 5 | concurrent_insert | ON | datadir | /home/mysql/data/ | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | join_buffer_size| 131072 | flush | OFF | flush_time | 0 | init_file | | interactive_timeout | 28800 | key_buffer_size | 402649088 | language| /usr/local/mysql/share/mysql/english/ | locked_in_memory| OFF | log | OFF | log_update | ON | log_bin | OFF | log_slave_updates | OFF | long_query_time | 10 | low_priority_updates| OFF | lower_case_table_names | 0 | max_allowed_packet | 1047552 | max_connections | 100 | max_connect_errors | 10 | max_delayed_threads | 20 | max_heap_table_size | 16777216 | max_join_size | 4294967295 | max_sort_length
