Re: optimizing count statement
Posted by:
Karolis
Date: October 12, 2005 08:46AM
Your query, small set (44 results):
mysql> explain SELECT COUNT(*) FROM ( SELECT 1 FROM INDEX_KEYS WHERE KeywordID IN (7, 1000) GROUP BY DocumentID HAVING COUNT(DISTINCT KeywordID) = 2) AS x;
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | INDEX_KEYS | index | INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | PRIMARY | 8 | NULL | 4984 | Using index |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
2 rows in set (0.05 sec)
Your query, large set (6204 results):
mysql> explain SELECT COUNT(*) FROM ( SELECT 1 FROM INDEX_KEYS WHERE KeywordID IN (7, 8) GROUP BY DocumentID HAVING COUNT(DISTINCT KeywordID) = 2) AS x;
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | INDEX_KEYS | index | INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | PRIMARY | 8 | NULL | 9304 | Using index |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
2 rows in set (0.04 sec)
Old query, small set:
mysql> explain SELECT COUNT(*) FROM ( SELECT 1 FROM INDEX_KEYS WHERE KeywordID IN (7, 1000) GROUP BY DocumentID HAVING COUNT(DISTINCT KeywordID) = 2) AS x;
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | INDEX_KEYS | index | INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | PRIMARY | 8 | NULL | 4984 | Using index |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
2 rows in set (0.04 sec)
Old query, big set:
mysql> explain SELECT COUNT(*) FROM ( SELECT 1 FROM INDEX_KEYS WHERE KeywordID IN (7, 8) GROUP BY DocumentID HAVING COUNT(DISTINCT KeywordID) = 2) AS x;
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | INDEX_KEYS | index | INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | PRIMARY | 8 | NULL | 9304 | Using index |
+----+-------------+------------+-------+--------------------------------------------------------+---------+---------+------+------+------------------------------+
2 rows in set (0.03 sec)
Hope this helps :)
Karolis