This is going to be a tough one to provide examples for... I have a MySQL 5.0 query similar to this:
SELECT a.field1, a.field2, b.field3 - bfield4 AS 'Balance', c.field5, c.field6, d.field7, e.field8
FROM tablea a
LEFT JOIN tableb b ON b.field1 = a.field4
LEFT JOIN tablec c ON a.field6 = CONCAT(c.field1, c.field2)
INNER JOIN tabled d ON a.field8 = d.field1
INNER JOIN tablee e ON a.field6 = e.field3
INNER JOIN tablef ON f.field7 = a.field1
WHERE 1 = 1 AND b.datefield IS NOT NULL
(It's going to be tough because I obviously cannot provide the data or actual tables/fields).
(the 1 = 1 is because the SQL statement is being built through a menu and this ensures the WHERE isn't messed up in how it's coded).
All joins are joined to the primary table (a).
Now running the above query works fine. Results returned in .21 seconds.
BUT, add a simple ORDER BY and it takes minutes (IF EVER - I'm still waiting) for the query to finish.
ORDER BY b.datefield
I tried creating an index on the b table, datefield but that didn't help. Any ideas why trying to order would cause such a massive delay?
Much as I'd love to, upgrading the database is simply not an option.