Hi,
This is a repost. Can anyone help me?
Where can I look for more clues as to why this select is slow? A MyISAMchk
will take the database down for too long,
so unless I know that it will give me some usefull information I am hesitant
to try it.
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;
+---------------+--------------+--------------+-----------+-------------+
| Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+---------------+--------------+--------------+-----------+-------------+
| log_id_idx | 1 | log_id | A | NULL |
| interface_idx | 1 | interface_id | A | 1836325 |
| soc_date_idx | 1 | soc_date | A | 427 |
| server_idx | 1 | server_id | A | 12 |
| q_idx | 1 | queue_id | A | 185 |
+---------------+--------------+--------------+-----------+-------------+
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
| 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
