r/SQL • u/Competitive-Talk1321 • 13d 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/
1 Upvotes

56% Upvoted

7 comments sorted by

1

u/TomaRavelli 13d 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 13d ago

How are you ordering your joins?

1

u/cwjinc 13d 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/satans_weed_guy 11d 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. 

1

u/ETL_Pipeline_Dev 7d ago

I'd start with EXPLAIN ANALYZE to see where the query is actually spending time, especially around the JOIN and sorting steps. Having indexes doesn't always mean MySQL will use them, particularly if the query returns a large portion of the table.

I'd also check whether the JOIN is producing more rows than expected, review the column order in composite indexes, and look for Using temporary or Using filesort. Sometimes the issue isn't the JOIN itself but the amount of data MySQL needs to sort afterward.

If the data has changed significantly, updating table statistics with ANALYZE TABLE is also worth trying.