Recently had a problem that made me scratch my head and think… what the hell is up?
I have a innodb table, 1Mil plus rows, news items, with some information on other tables, which require a JOIN.
Basically, it is a SELECT … FROM news INNER JOIN … ORDER BY id DESC LIMIT 10
The specific page was loading extremely slow, wierd, as I was sorting by an auto inc (INT) PK. I watched the query in MySQL Admin and was suprised to see the query creating (multiple) temporary tables!
Turns out this simple query was JOINING first (3 tables - 1Mil+ rows) and THEN sorting by PK.
To get around the problem I did:
SELECT … FROM (SELECT … FROM news ORDER BY id DESC LIMIT 10) AS tmp INNER JOIN …
I’m suprised however, that the MySQL optimizer doesn’t take the fastest route in the scenario, which seems quite a simple query to interpret.