MySql Left Join very slow query

Question

i have 2 tables of about 3000/4000 rows. I need to make a relation using Left Join for filter the results by searching a text or datetime. I need also to count a total row for pagination. When i run the query is epic slow, consider about 40/45 seconds by console and more than a minute from webpage. The relation is: for 1 of tableA have N in tableB. There is a solution for have a fast query? The sample query is:

SELECT X,Y,Z, (SELECT COUNT(*) 
     FROM tableB WHERE tableB.idTa=tableA.id) AS CountTB 
FROM tableA 
LEFT JOIN tableB ON tableA.id = tableB.idA
WHERE tableA.X LIKE'%mytext%' OR tableB.Z LIKE'%mytext%' 
GROUP BY tableA.id 
ORDER BY tableA.Y LIMIT 0,10

Thank you all

ON clause is present of course. only mistyped from my phone. Now it's correct and very slow :)

New update: If I change the relationship from LEFT JOIN to RIGHT JOIN it become pretty fast....why??

you missed `ON` clause for join. so this query not slow. it is broken — Alex, Jun 28 '16 at 14:11
`LEFT JOIN` ought to be using `ON` or `USING`, not `WHERE`. You should not need that nested-`SELECT`. That's where your time is going. — Mike Robinson, Jun 28 '16 at 14:12
That `LIKE` avoid index, so you should consider [**FULL TEXT**](https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html) instead. — Juan Carlos Oropeza, Jun 28 '16 at 14:13

Alex · Answer 1 · 2016-06-28T14:27:02.863

2

Start from correct JOIN I believe that should work pretty fast.

Something like:

SELECT a.X,a.Y,a.Z, COUNT(b.id)  CountTB 
FROM tableA a
LEFT JOIN tableB b
ON b.idTa = a.id AND b.Z LIKE'%mytext%' 
WHERE a.X LIKE '%mytext%'
GROUP BY tableA.id 
ORDER BY tableA.Y LIMIT 0,10

Your conditions are not very clear. If you can provide good data sample, we can find better query.

I have a guess that you don't need to b.Z LIKE'%mytext%'. start from just:

SELECT a.X,a.Y,a.Z, COUNT(b.id)  CountTB 
FROM tableA a
LEFT JOIN tableB b
ON b.idTa = a.id 
WHERE a.X LIKE '%mytext%'
GROUP BY tableA.id 
ORDER BY tableA.Y LIMIT 0,10

edited Jun 28 '16 at 14:27

answered Jun 28 '16 at 14:15

Alex

16,739
1
28
51

*Hmm,* I was not aware that you could use `AND` in an `ON`-clause like that. – Mike Robinson Jun 28 '16 at 14:21
Yes `AND` can be used in `ON`. But don't do it in this case. Use `ON` to specify how the tables are related; use `WHERE` to filter. – Rick James Jul 01 '16 at 16:16

score 1 · Answer 2 · answered Jun 28 '16 at 14:20

Taking a wild stab at this, just to point you in the general direction, here's a possible rewrite of that query: (I don't promise that it works ...)

SELECT X,Y,Z, COUNT(B.id) AS CountTB
FROM tableA A
  LEFT JOIN tableB B
    USING (id)
WHERE tableA.X LIKE'%mytext%' OR tableB.Z LIKE'%mytext%' 
GROUP BY X, Y, Z
ORDER BY tableA.Y 
LIMIT 0,10

Notice my many changes:

There is no nested query.
The tables are identified for convenience by the alises, A and B.
The LEFT JOIN is USING (id), which is shorthand for WHERE A.id = B.id.
The GROUP BY clause specifies that the data is to be grouped by these three variables. All of these must appear in the SELECT clause, and everything else that appears must be an aggregate function such as COUNT() or SUM().

Again, I do not promise that this query works. But it should illustrate how such a query should generally be constructed.

you can't `USING (id)` because in original post we have `WHERE tableB.idTa=tableA.id` so **idTa!=id** — Alex, Jun 28 '16 at 14:22

score 0 · Answer 3 · answered Jul 01 '16 at 16:34

Don't use LEFT unless you want NULLs from the 'righthand' table. I don't think you do in this case.

The positioning of the ( SELECT COUNT... ) means that it needs to be re-evaluated for ever row, which it does not. Get rid of it; use SQL_CALC_FOUND_ROWS.

A.X and B.Z are being searched for the same substring? Do they always have the same value? If so, then only search one of them. (LIKE '%...' is a costly part of your query.)

Using JOIN inflates the number of rows; then you added GROUP BY to deflate it. This inflate/deflate is costly.

The COUNT(*) fails to filter by '%mytext%', so it won't give you what you want for "rows 30-39 of 145".

Is it A.Z or B.Z??

SELECT SQL_CALC_FOUND_ROWS
        a.X, a.Y,
        ( SELECT Z FROM tableB WHERE idTa = a.id AND Z LIKE'%mytext%' ) AS Z
    FROM tableA AS a
    WHERE a.X LIKE '%mytext%'
    ORDER BY a.Y
    LIMIT 0,10

SELECT FOUND_ROWS();   -- to get the "of 156 rows"

and you need these

tableB: INDEX(idTa)
tableA: INDEX(Y)

Caveat: Since it is unclear what the relationship between A and B is (1:many vs many:many), this solution may be incorrect. The incorrectness will jump out by the subquery returning multiple rows.

So, I may not have given you the 'correct' answer, but hopefully I have given you enough clues so you can get much closer.

After that, perhaps you can make the pagination faster by "remembering where you left off" instead of using OFFSET. See my blog.

If you want to discuss this further, please provide SHOW CREATE TABLE and do not obfuscate the column names -- often the names give clues of your intent.

MySql Left Join very slow query

3 Answers3