Hallo,
ich bräuchte ein bisschen Hilfe beim Optimieren von folgender Abfrage:
Wenn ich das ausführe, dauert es 30+ Sekunden (forum_post hat ~ 1.5 Millionen und forum_topic fast 200000 Einträge). Ein EXPLAIN gibt mir das hier aus:
Ich nehme an, dass das "Using temporary; Using filesort" dafür verantwortlich ist, dass es so lange dauert.
Das sind die Indizes, die die forum_post-Tabelle hat:
Kann mir jemand helfen, die Abfrage annehmbar schnell zu machen?
Danke schonmal im Vorraus,
__abcde__
ich bräuchte ein bisschen Hilfe beim Optimieren von folgender Abfrage:
Code:
SELECT distinct(forum_topic.id) AS distinct_1 FROM forum_topic, forum_post WHERE forum_post.author_id = 3134 AND forum_post.topic_id = forum_topic.id AND forum_topic.forum_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) ORDER BY forum_topic.last_post_id DESC LIMIT 0, 30;
Code:
mysql> explain SELECT distinct(forum_topic.id) AS distinct_1 FROM forum_topic, forum_post WHERE forum_post.author_id = 3134 AND forum_post.topic_id = forum_topic.id AND forum_topic.forum_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) ORDER BY forum_topic.last_post_id DESC LIMIT 0, 30; +----+-------------+-------------+--------+-------------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+-------------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+---------------------------------+ | 1 | SIMPLE | forum_post | ref | forum_post_author_id,forum_post_topic_id,viewtopic,forum_post_egosearch | forum_post_egosearch | 4 | const | 3076 | Using temporary; Using filesort | | 1 | SIMPLE | forum_topic | eq_ref | PRIMARY,viewforum | PRIMARY | 4 | ubuntu_de_inyoka.forum_post.topic_id | 1 | Using where | +----+-------------+-------------+--------+-------------------------------------------------------------------------+----------------------+---------+--------------------------------------+------+---------------------------------+ 2 rows in set (0.00 sec)
Das sind die Indizes, die die forum_post-Tabelle hat:
Code:
mysql> show indexes from forum_post; +------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | forum_post | 0 | PRIMARY | 1 | id | A | 1640642 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | forum_post_author_id | 1 | author_id | A | 205080 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | forum_post_topic_id | 1 | topic_id | A | 273440 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | viewtopic | 1 | topic_id | A | 546880 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | viewtopic | 2 | position | A | 1640642 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | forum_post_pub_date | 1 | pub_date | A | 1640642 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | forum_post_egosearch | 1 | author_id | A | 6459 | NULL | NULL | | BTREE | NULL | | forum_post | 1 | forum_post_egosearch | 2 | id | A | 1640642 | NULL | NULL | | BTREE | NULL | +------------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (1.04 sec)
Danke schonmal im Vorraus,
__abcde__
Comment