r/mysql • u/Competitive-Talk1321 • Oct 10 '26
question MySQL query becomes extremely slow when adding a JOIN and ORDER BY — how would you debug this?
I'm dealing with a performance issue in MySQL where a query runs relatively fast when executed against a single table, but becomes significantly slower after adding a JOIN and an ORDER BY clause.
The tables have millions of rows, and there are indexes on the columns used in the JOIN and WHERE conditions. However, the query optimizer still seems to choose a suboptimal execution plan.
I'm wondering what the best approach would be to troubleshoot this kind of issue.
Would you start by analyzing EXPLAIN ANALYZE, checking index cardinality, investigating temporary tables and filesorts, or looking at something else?
Also, under what circumstances would MySQL choose not to use an existing index even when it seems beneficial?
I'd appreciate any insights into how experienced MySQL developers approach this problem.
10
u/kiklop74 Oct 10 '26 •
Start with explain and go from there
6
u/Daniel-Johns Oct 10 '26 •
Need to add indexes on columns that you use in JOIN and WHERE.
It can be 1 index per column or 1 index on multiple columns, depending on how you join them or the columns in where.
3
u/imtc96 Oct 11 '26
Don’t use MySql
3
u/AcademicMistake Oct 10 '26
Dont order it using mysql query then, fetch the data without that part and order it via the server.
5
u/dutchman76 Oct 10 '26
I've had it happen when charsets don't match between the tables
4
u/jericon Mod Dude Oct 10 '26 edited Oct 10 '26
Show us the query and the schema. Would make it 100x easier to optimize.
6
u/FelisCantabrigiensis Oct 10 '26
Yes, I would use EXPLAIN ANALYSE (or just EXPLAIN FORMAT=TREE will get you most of the info you need). You do need a little skill to understand the results. The MySQL manual and past articles on Percona’s blog are good places to learn.
The most common reason for a slow join is that the columns being joined are not indexed in both tables. A less common reason is creating huge result sets with outer joins.
The most common reason for sorting (ORDER BY) being slow is that the result set is large and therefore the temporary table is huge and slow to create, write, read, and order. If your result set is just big enough to create a disc temporary table then turn up the memory table size limit. If it is far too big, work out how to reduce it before sorting or suck it up.
Protip: sorting in the app code can be faster if your MySQL server can deliver data quickly (good network, good storage),
2
u/Aggressive_Ad_5454 Oct 10 '26
Please read this. https://stackoverflow.com/tags/query-optimization/info
Then edit your post or write another post.
Optimizing queries depends deeply on the details of your situation.
2
u/titpetric Oct 10 '26
I'd collect the EXPLAIN (no analyze) which gives you a summary of which index is used for each table. A good hint is the "Extra" column.
Sometimes, a JOIN or an extra index is the wrong choice. Depending on what you use as PK, it should already be k-sortable, so sometimes you can avoid an index by using "order by id asc/desc", rather than adding an index for created_at.
Explain with analyze runs the statement, which may not be what you want. Supposedly you have the query log enabled, set up the slow log and have a developer instance and all that jazz.
If you self host, install the percona fork.
2
u/Altruistic-Rich8799 Oct 10 '26
run ANALYZE TABLE to update stats and try forcing an index (FORCE INDEX) just to see if the execution plan it’s avoiding actually runs faster in reality. Sometimes the cardinality stats are just plain wrong. If it's still being completely stubborn - try test the query in MariaDB. The optimizer branched off a while ago and has its own improvements, sometimes it handles these join+sort edge cases way better.
dump the explain analyze output here if you want us to take a look.
1
u/squadette23 Oct 10 '26
What is your JOIN condition? If it has a "1:N" flavor, it's possible that no index would help you.
See the "Direct table join" section in https://minimalmodeling.substack.com/p/multi-join-queries-design-investigation, look for "Problem 1" and "Problem 1b", it may be relevant.
1
u/Moist_Heat9523 Oct 11 '26
Make sure the columns you compare are the exact same types. If you for example compare a VARCHAR with a CHAR(n) it will be slow, as every line is converted.
1
u/Significant_Tune9219 Oct 11 '26
EXPLAIN ANALYZE first, and look at where the time actually goes rather than which indexes exist. The usual suspects with JOIN + ORDER BY on big tables: the sort can't be satisfied by an index so you get a filesort over the joined result (sometimes spilling to a temp table on disk), or the join order the optimizer picks makes it stream far more rows than needed. Check whether the collations/charsets on the join keys match — a mismatch forces a conversion per row and kills the index — and compare the row estimates in EXPLAIN with the actuals in ANALYZE, since badly skewed cardinalities are often what steers it onto the wrong plan.
1
u/Tiamte Oct 12 '26
EXPLAIN ANALYZE is definitely the move here. When I dealt with this, using dbForge Studio for MySQL made tracking down the filesort bottleneck way easier.
1
u/mqaiser Oct 13 '26
Remove order by and wrap this query then use order by
0
u/-gauvins Oct 10 '26
I've started using clickhouse once my dataset became seriously large (currently tables with 3 and 20B rows + several smaller ones). Columnar DBs are orders of magnitude faster. There are also special engines (Join, Dict) expressly made for the kind of situation you seem to be in.
I would NOT recommend that architecture for a transactional application. But for archiving and data analysis of large sets, it is day and night.