MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3928
October 11, 2005 11:30AM
1833
October 12, 2005 04:56AM
1758
October 12, 2005 06:31AM
1438
October 12, 2005 07:11AM
1639
October 12, 2005 07:40AM
1556
October 12, 2005 07:53AM
1726
October 12, 2005 07:50AM
1707
October 12, 2005 08:01AM
1698
October 12, 2005 08:05AM
1432
October 12, 2005 08:23AM
1720
October 12, 2005 08:22AM
1671
October 12, 2005 08:38AM
Re: optimizing count statement
1794
October 12, 2005 08:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.