Re: Slow Query Time for Many Records from Large Table

[John Lombardi]

Thanks, Jay. As a starting point, this query taking the time it does concerns me. The select and the explain are shown below...

mysql> select SQL_NO_CACHE count(*) FROM table WHERE AID = '3';
+----------+
| count(*) |
+----------+
| 40997 |
+----------+
1 row in set (0.06 sec)

mysql> explain select SQL_NO_CACHE count(*) FROM table WHERE AID = '3';
+------------------+------+---------------+------+---------+-------+-------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------------+------+---------------+------+---------+-------+-------+--------------------------+
| table | ref | idx1 | idx1 | 2 | const | 47925 | Using where; Using index |
+------------------+------+---------------+------+---------+-------+-------+--------------------------+
1 row in set (0.01 sec)

This was taken from a test server where there are no INSERTs or UPDATEs occuring. Thanks!