Re: Slow Query Time for Many Records from Large Table

[John Lombardi]

The 0.06 seems on the long side given that it is a simple count from an index (though, on a large table, counting a lot of records). This statement was a simpllified query for illustration purposes. The actually application does several more complex queries and relies on temporary tables. Thus, the long response times hurt due to temporary tables not being query cached.

We have 600M set for our key buffer.

To keep things simple, below is the heart of the join which takes about 0.43. The actual query is more complex as it involves another join, though this one should hopefully be representative of our issue.

mysql> SELECT SQL_NO_CACHE count(*) FROM table AS t1, table AS t2, table AS t3, table AS t4 WHERE t1.BID = t2.BID AND t1.BID = t3.BID AND t1.BID = t4.BID AND t1.AID = '3' AND t2.AID = '4' AND t3.AID = '5' AND t4.AID = '6';
+----------+
| count(*) |
+----------+
| 20982 |
+----------+
1 row in set (0.43 sec)

+----------+--------+---------------+------+---------+--------------------------+-------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+--------+---------------+------+---------+--------------------------+-------+--------------------------+
| t2 | ref | idx1,idx2 | idx1 | 2 | const | 24676 | Using where; Using index |
| t1 | eq_ref | idx1,idx2 | idx1 | 5 | const,t2.BID | 1 | Using where; Using index |
| t3 | eq_ref | idx1,idx2 | idx1 | 5 | const,t1.BID | 1 | Using where; Using index |
| t4 | eq_ref | idx1,idx2 | idx1 | 5 | const,t1.BID | 1 | Using where; Using index |
+----------+--------+---------------+------+---------+--------------------------+-------+--------------------------+
4 rows in set (0.00 sec)


Thanks for giving this a look.