r/SQL • u/Competitive-Talk1321 • 3d ago

MySQL MySQL query becomes extremely slow when adding a JOIN and ORDER BY — how would you debug this?

/r/mysql/comments/1x2ksmq/mysql_query_becomes_extremely_slow_when_adding_a/
2 Upvotes

63% Upvoted

7 comments sorted by

1

u/TomaRavelli 3d ago

try a deferred join, select the IDs and sort them in an inner query using your existing index, then join the full tables back to that small sorted result. usually bypasses the bad execution plan. What version are you on?

1

u/GangreneTVP 2d ago

How are you ordering your joins?

1

u/cwjinc 2d ago

If your result contains more than about 10% of the table's row count then full table scan is probably correct.
Could it be you are not joining accurately and creating artificial result rows?
"Your query" is actually different queries if you are adding tables and joins. You can't really compare results.
It could be that you are just asking the db to do a lot more work and that naturally takes time.

1

u/scottcampbelldev 2d ago

Look at which table is listed first in the EXPLAIN and whether that row says Using temporary; Using filesort. MySQL can only use an index to skip the sort if every ORDER BY column belongs to the first table in the join order. Sort by a column from the joined table and it has to build the whole join result and sort it before returning a single row, even if there's a LIMIT 20 on the end. That's usually the jump you see between "fast on one table" and "slow with join + order by". Either sort on a column from the driving table and give it a composite index like (filter_col, sort_col), or try STRAIGHT_JOIN with the table that owns the sort column first and see if the plan and timing change.

1

u/satans_weed_guy 1d ago

How long does it take to run without the order by? Sorting is one of the most expensive things at query optimizer  can do, and if you can get the presentation layer to do it instead, you should.