Re: optimizing count statement

[Karolis]

Sure. Here is a fast example:

mysql> SELECT COUNT(*) AS Cnt FROM INDEX_KEYS T1, INDEX_KEYS T2 WHERE (T1.KeywordID = 7 AND T2.KeywordID = 79 ) AND (T1.DocumentID = T2.DocumentID );
+------+
| Cnt |
+------+
| 1103 |
+------+
1 row in set (0.02 sec)




Here is a slow example:
mysql> SELECT COUNT(*) AS Cnt FROM INDEX_KEYS T1, INDEX_KEYS T2 WHERE (T1.KeywordID = 7 AND T2.KeywordID = 8 ) AND (T1.DocumentID = T2.DocumentID );
+------+
| Cnt |
+------+
| 6204 |
+------+
1 row in set (1.42 sec)



Here is EXPLAIN query of the slow example:
Not sure if It can be read on this forum though...
+----+-------------+-------+--------+----------------------------------------------------------------+---------------------+---------+----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------+---------------------+---------+----------------------------+------+--------------------------+
| 1 | SIMPLE | T2 | ref | PRIMARY,INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | INDEXKEYS_KeywordID | 4 | const | 4364 | Using where; Using index |
| 1 | SIMPLE | T1 | eq_ref | PRIMARY,INDEXKEYS_KeywordID,index_PR_KeyRank,index_PR2_KeyRank | PRIMARY | 8 | search.T2.DocumentID,const | 1 | Using where; Using index |
+----+-------------+-------+--------+----------------------------------------------------------------+---------------------+---------+----------------------------+------+--------------------------+


Table Structure:
mysql> DESCRIBE INDEX_KEYS;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| DocumentID | int(11) | | PRI | 0 | |
| KeywordID | int(11) | | PRI | 0 | |
| KeyDensity | int(11) | | | 0 | |
| KeyWeight | double | | | 0 | |
| KeyTitleDensity | int(11) | | | 0 | |
| KeyTitleWeight | double | | | 0 | |
| KeyLinkDensity | int(11) | | | 0 | |
| KeyLinkWeight | double | | | 0 | |
| KeyHeadingDensity | int(11) | | | 0 | |
| KeyHeadingWeight | double | | | 0 | |
| KeyDocumentRank | double | | | 0 | |
| PR_KeyRank | double | | | 0 | |
| PR2_KeyRank | double | | | 0 | |
+-------------------+---------+------+-----+---------+-------+
13 rows in set (0.00 sec)


Indexes:
mysql> SHOW INDEX FROM INDEX_KEYS;
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| INDEX_KEYS | 0 | PRIMARY | 1 | DocumentID | A | 14766 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 0 | PRIMARY | 2 | KeywordID | A | 590652 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | INDEXKEYS_KeywordID | 1 | KeywordID | A | 24610 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR_KeyRank | 1 | KeywordID | A | 84378 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR_KeyRank | 2 | DocumentID | A | 590652 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR_KeyRank | 3 | PR_KeyRank | A | 590652 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR2_KeyRank | 1 | KeywordID | A | 4018 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR2_KeyRank | 2 | DocumentID | A | 590652 | NULL | NULL | | BTREE | |
| INDEX_KEYS | 1 | index_PR2_KeyRank | 3 | PR2_KeyRank | A | 590652 | NULL | NULL | | BTREE | |
+------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set (0.01 sec)

Karolis