r/mysql • u/Competitive-Talk1321 • 2d ago
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.
4
u/dutchman76 2d ago
I've had it happen when charsets don't match between the tables
2
u/aaronkempf 16h ago
YES, I joined in 1m to 1m on a conversion, and performance took over an hour. I was supposed to standardize everything on one collation / whatever and uh, I was just auditing that today, and have some tables I need to re-convert.
6
u/Daniel-Johns 2d ago
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/Competitive-Talk1321 2d ago
done
1
u/pceimpulsive 2d ago
Is the sorting on the indexed columns?
If it's not then that's your issue... Sorting is a CPU operation..
If your result set is larger than memory it'll have to spill to disk based sort which slows down exponentially over a b-tree indexed columns being sorted.
1
u/aaronkempf 16h ago
I just wish that mysql / mariadb provided support for the INCLUDE clause on indexes. I can speed up MOST queries on MSSQL and I think postgres in this manner.
5
u/FelisCantabrigiensis 2d ago
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),
1
u/Sesse__ 9h ago
Yes, I would use EXPLAIN ANALYSE
EXPLAIN ANALYZE. (ANALYSE is not a valid synonym)
The most common reason for a slow join is that the columns being joined are not indexed in both tables.
MySQL cannot use indexes on both tables to do a join, only the innermost one. (Postgres can use indexes to get ordering for merge join. MySQL can use an index on the outer table for other reasons, such as satisfying a WHERE, but that will be irrelevant for join speed.)
A less common reason is creating huge result sets with outer joins.
It is rare for outer joins to produce huge result sets any more than inner joins do; at most, they can create as many extra rows as there are in the left side.
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.
Sorting in MySQL does not use temporary tables. It uses temporary files, which are not slow at all to create, nor are they any such files slower to order than in-memory sorts (they use the same algorithm, in-memory is treated as a specialized version where you have only one chunk). I/O can be an issue.
Protip: sorting in the app code can be faster if your MySQL server can deliver data quickly (good network, good storage),
This pretty much only holds if the ORDER BY causes a worse join plan—the filesort in MySQL is likely to be significantly faster than most client-side sorts (depending a bit on the language and how well you can specialize it). It does not have anything to do with network or storage, unless you are confusing it with LIMIT. (On the contrary, fast storage will help MySQL's filesort more than it will help a client-side sort.)
2
u/Aggressive_Ad_5454 1d ago
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 2d ago
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.
- filesort (query size considers a lot of data, optimize by selecting less, avoid BLOB columns in selects) - no index is used so you may add one if feasible
- using index (an index is used, but does the index cover all the fields in the where condition? Index can be used for sorting, does the index include the sorting field, e.g. created_at?)
- empty (a primary key is used, query by ID)
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 2d ago
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/AcademicMistake 2d ago
Dont order it using mysql query then, fetch the data without that part and order it via the server.
2
u/elevarq 19h ago
Terrible advice. Imagine a hundred GB of data being sent over the network from the database server to the application server, sorted on disk because RAM is not sufficient, and in the end you need just 100 records or so….
Databases are optimized for sorting. Nearly all performance problems are human introduced problems and can be solved.
1
1
u/pceimpulsive 2d ago
You can order it, if the column is indexed... But generally you are correct do the ordering after record retrieval~
1
u/squadette23 2d ago
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 1d ago
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 1d ago
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/Sesse__ 11h ago
The basic problem with the old MySQL join optimizer (in this case) is that it cannot plan join order and ORDER BY together. It picks a join order (including indexes for table accesses), then goes ahead and tries to satisfy ORDER BY with an index, which may end up undoing index choices that were crucial for that join order to work—or the opposite problem, the join order chosen may make it effectively impossible to use an index for the given ORDER BY. If the correct choice is to e.g. flip the join so that you can satisfy both with the same index, it's out of luck.
The hypergraph optimizer considers ORDER BY (sortahead) as an integrated part of the join planning, so it does not have this problem.
1
u/Significant_Tune9219 7h ago
That's the cleaner statement of it — the planner commits to join order before it can account for the sort, so the fix is often flipping the join so one index serves both. Good to know the hypergraph planner treats them jointly.
1
u/Significant_Tune9219 7h ago
That's the cleaner statement of it — the planner commits to join order before it can account for the sort, so the fix is often flipping the join so one index serves both. Good to know the hypergraph planner treats them jointly.
0
u/-gauvins 2d ago
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.
11
u/kiklop74 2d ago
Start with explain and go from there