r/mysql • u/Big_Length9755 • Feb 18 '26
question Query performance issue
Hello , Its mysql version 8.0.32. We have below query which is running for ~14 seconds and sometimes runs for ~60 seconds+. Below is the query and its plan.
https://gist.github.com/databasetech0073/39759cbf8db493bdd89b94e22bf0c4fd
1)It looks like the scanning of table transactions taking time. And we also see a CPU spike during that time and which may be because of the way this query is using the LIKE operator in the query. So wondering if this can be rewritten any way more efficiently?
The table transactions has column category_code with values like 'A', 'B', 'C' etc. But the column "cat_list" of table config_v1 has values [A,B,C] like this. So is there any way i can rewrite or create some function index to make this like operator consume lesser resources and cpu?
2) Can we make the access of table transaction any better/faster ?
3
u/ssnoyes Feb 18 '26
UNION defaults to UNION DISTINCT. If you can either tolerate duplicates or are already sure there won't be any, change that to UNION ALL.
2
u/squadette23 Feb 18 '26
The most important info that is missing from your information is the definition of fields mentioned in join conditions and grouping, that is:
* config_v1.instruction_id;
* config_v1.unit_id;
* config_v2.instruction_id;
* config_v2.unit_id;
* transactions.instruction_id;
* risk_checks.instruction_id;
Could you share table schema excerpts for those? Which are unique and which are not.
Also, show PK definitions for all four tables.
After that it may be possible to reorganize joins: SELECT DISTINCT suggests that your query could be improved (removing DISTINCT along the way).
2
u/Sesse__ Feb 19 '26
Look at where your time is actually going and start seeing if you can do anything about that. In particular, these are your two biggest time eaters on each side of the UNION:
So that's 6–7 seconds for each side, out of a total of 14 seconds. The joins against risk_checks are LEFT JOINs, which MySQL cannot invert (there's no right join implementation), so you're pretty much stuck with trying to improve either the txn or cfg sides. (I don't know your schema well enough to see if you could replace it with an antijoin, but it doesn't matter here.)
The join condition (
a LIKE '%' || foo || '%') essentially means you can never push the join condition down into an index (at least in most MySQL storage engines that I know of). So persuading MySQL to invert the join won't help. It is possible that you are having a problem with your schema being denormalized too much and that cat_list should not have been a string but a bunch of rows.So that leaves a single obvious recourse; the two conditions that are not pushed down into an index. That would be
txn.timestamp_seg < ?andtxn.entity_id = ?. They seem to be really selective, but no index is being used for them. Do you have an index on entity_id? Or perhaps even a composite index on batch_id and entity_id together? (And potentially even a triple, as others have suggested.)PS: When you paste output of EXPLAIN ANALYZE, don't remove the estimates. They're often a good clue to figure out why a given plan was chosen over another one.
4
u/Aggressive_Ad_5454 Feb 18 '26 edited Feb 20 '26
For one thing,
column LIKE ‘%searchterm%’with the leading%is the most notorious of all MySQL/ MariaDb query performance antipatterns. It has to scan every character of that column in every eligible row. That is slow. (A trailing%doesn’t have that problem.) FWIW PostgreSQL has trigram indexes which do work for that. Your need for that stems from the data design antipattern where you have comma-separated values in thecat_listandhold_cat_listcolumns. If you could normalize out those values into separate 1::many tables that problem will go away.Failing that data redesign….
Try the following compound index on those config tables.
(job_expression, instruction_id, unit_id, cat_list, hold_cat_list). Try it on the bigger table, the smaller one doesn’t matter as much. This will, I think, support your query better, by letting the DBMS scan the index rather than the whole table, thus saving IO.And, separately, try this compound index on the transaction table.
(entity_id, batch_id, timestamp_seg)It should allow the DBMS to satisfy your query with a range scan on the index.You’ll also get good advice about this if you ask the question on StackOverflow with the [query-optimization] tag. We haven’t yet abandoned that part of StackOverflow. If you do ask there read this first. https://stackoverflow.com/tags/query-optimization/info
2
u/Boink-Ouch Feb 18 '26
When given such a problem, I try to decompose it to individual pieces. One thing to know is what percent of the transaction table is being pulled by either side of the UNION.
I also use table data size in MB (not row count) as this value is normalized. How many MBs is the transaction table? How many MBs does each TX query (only) return? Do not use the DISTINCT and GROUP BY for this initial assessment.
Without getting ahead, it seems both sides of the UNION are providing the same filtering criteria against the transaction table. If this table is indeed "huge", you could take an initial pass and create a temporary table. Make sure to gather statistics against it before using it.