Query-Optimierung

[__abcde__]

Hallo,

ich bräuchte ein bisschen Hilfe beim Optimieren von folgender Abfrage:

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;

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:

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)

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:

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)

Kann mir jemand helfen, die Abfrage annehmbar schnell zu machen?

Danke schonmal im Vorraus,

__abcde__

 

[Bieber]

hilft es was, wenn du die zwei AND-Anweisungen vertauschst?
ich weiß leider nicht wie sql intern arbeitet, aber wenn es schritt für schritt die Abfragen durchgeht, wäre es sinnvoller zuerst die IN-Bedingung zu erfüllen, weil dann für den Vergleich weniger Werte verglichen werden müssen :d

ich hoffe du verstehst was ich meine^^

 

[ZeitGeist]

kenne mich auch nicht mit der Optimierung in MySQL aus, aber vielleicht hilfts einen Join zu verwenden (wenn der Optimierer das nicht schon selber erkennt).

SELECT distinct(forum_topic.id) AS distinct_1
FROM forum_topic JOIN forum_post ON (forum_post.topic_id = forum_topic.id)
WHERE forum_post.author_id = 3134
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;

auf forum_topic.id sollte auch ein Index sein (wenns nicht schon primary key ist)