Mysql langsame Abfrage: INNER JOIN + ORDER BY verursacht filesort

Question

Ich versuche, diese Abfrage zu optimieren:

SELECT \`posts\`.\* FROM \`posts\` INNER JOIN \`posts\_tags\` 
     ON \`posts\`.id = \`posts\_tags\`.post\_id 
     WHERE  (((\`posts\_tags\`.tag\_id = 1))) 
     ORDER BY posts.created\_at DESC;

Die Größe der Tabellen ist 38k Zeilen, und 31k und mysql verwendet "filesort", so wird es ziemlich langsam. Ich habe versucht, verschiedene Indizes zu verwenden, kein Glück.

CREATE TABLE \`posts\` (
  \`id\` int(11) NOT NULL auto\_increment,
  \`created\_at\` datetime default NULL,
  PRIMARY KEY  (\`id\`),
  KEY \`index\_posts\_on\_created\_at\` (\`created\_at\`),
  KEY \`for\_tags\` (\`trashed\`,\`published\`,\`clan\_private\`,\`created\_at\`)
) ENGINE=InnoDB AUTO\_INCREMENT=44390 DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci

CREATE TABLE \`posts\_tags\` (
  \`id\` int(11) NOT NULL auto\_increment,
  \`post\_id\` int(11) default NULL,
  \`tag\_id\` int(11) default NULL,
  \`created\_at\` datetime default NULL,
  \`updated\_at\` datetime default NULL,
  PRIMARY KEY  (\`id\`),
  KEY \`index\_posts\_tags\_on\_post\_id\_and\_tag\_id\` (\`post\_id\`,\`tag\_id\`)
) ENGINE=InnoDB AUTO\_INCREMENT=63175 DEFAULT CHARSET=utf8
    +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
| id | select\_type | table      | type   | possible\_keys            | key                      | key\_len | ref                 | rows  | Extra                                                     |
+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | posts\_tags | index  | index\_post\_id\_and\_tag\_id | index\_post\_id\_and\_tag\_id | 10      | NULL                | 24159 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | posts      | eq\_ref | PRIMARY                  | PRIMARY                  | 4       | .posts\_tags.post\_id |     1 |                                                           | 
+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Welche Art von Index muss ich definieren, um zu vermeiden, dass mysql mit filesort? Ist es möglich, wenn das Feld order nicht in der where-Klausel enthalten ist?

aktualisieren: Profiling-Ergebnisse:

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000027 | 
| checking query cache for query | 0.037953 | 
| Opening tables                 | 0.000028 | 
| System lock                    | 0.010382 | 
| Table lock                     | 0.023894 | 
| init                           | 0.000057 | 
| optimizing                     | 0.010030 | 
| statistics                     | 0.000026 | 
| preparing                      | 0.000018 | 
| Creating tmp table             | 0.128619 | 
| executing                      | 0.000008 | 
| Copying to tmp table           | 1.819463 | 
| Sorting result                 | 0.001092 | 
| Sending data                   | 0.004239 | 
| end                            | 0.000012 | 
| removing tmp table             | 0.000885 | 
| end                            | 0.000006 | 
| end                            | 0.000005 | 
| query end                      | 0.000006 | 
| storing result in query cache  | 0.000005 | 
| freeing items                  | 0.000021 | 
| closing tables                 | 0.000013 | 
| logging slow query             | 0.000004 | 
| cleaning up                    | 0.000006 | 
+--------------------------------+----------+

update2:

Echte Abfrage (einige weitere boolesche Felder, weitere nutzlose Indizes)

SELECT \`posts\`.\* FROM \`posts\` INNER JOIN \`posts\_tags\` 
   ON \`posts\`.id = \`posts\_tags\`.post\_id 
   WHERE ((\`posts\_tags\`.tag\_id = 7971)) 
       AND (((posts.trashed = 0) 
       AND (\`posts\`.\`published\` = 1 
       AND \`posts\`.\`clan\_private\` = 0)) 
       AND ((\`posts\_tags\`.tag\_id = 7971)))  
   ORDER BY created\_at DESC LIMIT 0, 10; 

Leerer Satz (1,25 Sek.)

Ohne ORDER BY - 0,01s.

+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
| id | select\_type | table      | type   | possible\_keys                           | key                   | key\_len | ref                 | rows  | Extra                    |
+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
|  1 | SIMPLE      | posts\_tags | index  | index\_posts\_tags\_on\_post\_id\_and\_tag\_id  | index\_posts\_tags\_...  | 10      | NULL                | 23988 | Using where; Using index | 
|  1 | SIMPLE      | posts      | eq\_ref | PRIMARY,index\_posts\_on\_trashed\_and\_crea | PRIMARY               | 4       | .posts\_tags.post\_id |     1 | Using where              | 
+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+

LÖSUNG

1. Abfrage aktualisiert auf "ORDER BY posts_tags.created_at DESC" (zwei kleine Änderungen im Programmcode)
2. Index hinzugefügt: index_posts_tags_on_created_at.

Das ist alles!