I have a slow query running on a large forum. The EXPLAIN synopsis:
mysql> EXPLAIN
SELECT
COUNT(post_thanks.id) as thanked,
post_thanks.postid,
post.title,
post.dateline
FROM
post_thanks
INNER JOIN post ON post_thanks.postid = post.postid
WHERE
post.threadid = 562942
AND post.parentid != 0
GROUP BY post_thanks.postid
ORDER BY
COUNT(post_thanks.id) DESC,
dateline DESC LIMIT 5
;
+----+-------------+-------------+------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------+
| 1 | SIMPLE | post | ref | PRIMARY,idx_parentid,idx_ksours | idx_ksours | 4 | const | 7826 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | post_thanks | ref | postid | postid | 4 | forum.post.postid | 1 | Using where; Using index |
+----+-------------+-------------+------+---------------------------------+------------+---------+-------------------+------+----------------------------------------------+
The indexes on the table:
mysql> SHOW INDEXES FROM post;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| post | 0 | PRIMARY | 1 | postid | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | ipaddress | 1 | ipaddress | A | 6509487 | NULL | NULL | | BTREE | | |
| post | 1 | userid | 1 | userid | A | 1017107 | NULL | NULL | | BTREE | | |
| post | 1 | userid | 2 | dateline | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | idx_dateline | 1 | dateline | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ipaddress | 1 | ipaddress | A | 6509487 | NULL | NULL | | BTREE | | |
| post | 1 | idx_parentid | 1 | parentid | A | 16273717 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 1 | threadid | A | 1713022 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 2 | visible | A | 1713022 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 3 | dateline | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 4 | userid | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 5 | postid | A | 32547435 | NULL | NULL | | BTREE | | |
| post | 1 | idx_ksours | 6 | attach | A | 32547435 | NULL | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.01 sec)
I've been trying to figure out why it is not using the index from the post table.
mysql> SHOW INDEXES FROM post_thanks;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| post_thanks | 0 | PRIMARY | 1 | id | A | 7177648 | NULL | NULL | | BTREE | | |
| post_thanks | 1 | postid | 1 | postid | A | 7177648 | NULL | NULL | | BTREE | | |
| post_thanks | 1 | idx_userid | 1 | userid | A | 797516 | NULL | NULL | | BTREE | | |
| post_thanks | 1 | idx_userid | 2 | date | A | 7177648 | NULL | NULL | | BTREE | | |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
Does anyone have ideas as to why it is not using the index(es), or how I can attack this problem to make the query faster? Even if I remove the parentid portion, it still does not use the idx_ksours index.
* Warning, I'm not a DBA