Hi,
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.
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.
Comment