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 | 1024
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 67108864
| net_buffer_length | 16384
| net_retry_count | 10
| pid_file | /home/mysql/data/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 2093056
| server_id | 0
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /tmp/mysql.sock
| sort_buffer | 2097144
| table_cache | 512
| table_type | MYISAM
| thread_stack | 65536
| thread_cache_size | 8
| timezone | GMT
| tmp_table_size | 1048576
| tmpdir | /home/mysql/data/tmp/
| version | 3.23.27-beta-log
| wait_timeout | 28800
|+-------------------------+--------------------------------
---------------------------------------------------------------------
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
