Mysql query very slow when selecting from 2 tables

Question

I am trying to execute a very simple query with MySQL. What I am trying to achieve is to select some data from 2 different tables in 1 query. But if I select data from just 1 table the query runs fast, but when I switch it to select data from both tables it becomes very slow.

My query that I want to achieve looks like this:

SELECT k.klantId, b.bestelId
FROM klanten k, bestellingen b
WHERE k.klantId=b.klantId AND voornaam LIKE '%henk%'

The query above takes about 20 seconds to run.

But when I execute my query like this, the query only runs in less that a second:

SELECT k.klantId
FROM klanten k, bestellingen b
WHERE k.klantId=b.klantId AND voornaam LIKE '%henk%'

I also tried to select a different column from table 'bestellingen' but that is also very slow.

Does anyone understand how it can be so slow, with selecting an extra field ?

--------------------- EDIT ---------------------

I tought I was there, but now I was expending my table with more columns to select. Now it takes 18 seconds again, but maybe I am doing something wrong with my expanded query. Does anyone see what is wrong with this?

SELECT filiaalId, bestelId, k.klantId, totaalPrijs, b.statusId, b.tmInvoer, geprint, verzendwijze, betaalwijze, afhaalpuntId, verzendkosten, betaalwijzeKosten 
FROM klanten k LEFT JOIN bestellingen b ON (k.klantId=b.klantId) 
WHERE (k.voornaam LIKE '%henk%' OR k.achternaam LIKE '%henk%' OR b.bestelId LIKE '%henk%') 
ORDER BY b.tmInvoer DESC

Try an `INNER JOIN` instead of a cross join. Cartesian products are not to be recommended. — Gruber, Jun 22 '16 at 09:10
use explicit joins not old style joins via a where clause. Not that that is your problem, it is just that no one codes like this anymore (at least not going into an interview) — Drew, Jun 22 '16 at 19:37
I suspect the reason it can work well is that the 2nd query can be executed just using the indexes. Can you post your full table declares (with the indexes)? — Kickstart, Jun 23 '16 at 13:31

score 5 · Accepted Answer · answered Jun 22 '16 at 09:10

5

Try using a mysql join, this should be faster.

SELECT k.klantId, b.bestelId
FROM klanten k LEFT JOIN bestellingen b ON (k.klantId=b.klantId)
WHERE voornaam LIKE '%henk%'

You could also make sure the the two columns k.klantId and b.bestelId are indexed.

answered Jun 22 '16 at 09:10

cb0

8,415
9
52
80

This is perfect, it is working. Could you maybe explain me why it is faster ? – user3824329 Jun 22 '16 at 09:14
There are other good questions discussing this fact. Please look [here](https://stackoverflow.com/questions/1129923/is-a-join-faster-than-a-where) or [here](https://dba.stackexchange.com/questions/42998/are-individual-queries-faster-than-joins). The query optimizer is able to generate an better execution plan using joins than using where on multiple tables. – cb0 Jun 22 '16 at 09:26
Could you have another look I edited my post, cause if I expand my query it becomes slow again. Am I doing something wrong ? – user3824329 Jun 22 '16 at 09:34
Ok now you've added more `WHERE` clauses and they slow down the query. Make sure you index the columns you want to search. You could create an index for you specific search like this. `create index name_idx on klanten(voornaam, achternaam); create index bestellingen_idx on bestellingen(bestelId);` This should make your added query faster, but will also increase the storage size of the table, as mysql has to save the index. – cb0 Jun 22 '16 at 09:40
I already had the Indexes, is there no other way to speed up this query ? – user3824329 Jun 22 '16 at 09:49
I don't think there is another way. You are using the `LIKE %..%` keyword which is much slower than an `=` search. If you could eleminate this `LIKE` it "should" be much faster. – cb0 Jun 22 '16 at 09:57
you can use fulltext search for the brave of heart. You can have success with indexing if the string starts out @ the beginning of `voornaam ` being indexed. Otherwise it is a tablescan. – Drew Jun 22 '16 at 19:32
Using an explicit join in MySQL shouldn't make any performance difference at all. This example uses a LEFT OUTER JOIN which is likely to be less efficient than an INNER JOIN which would slow this down further (and MySQL doesn't support any implicit join syntax for OUTER JOINs) – Kickstart Jun 23 '16 at 15:41

score 1 · Answer 2 · answered Jul 28 '23 at 14:44

I know this is terribly late to your question, by about 7 years, but someone else will definitely have this issue. The solution is simple.

Once you "LIKE" especially with wild "%" from more than one table, you get a massive slow down. Changing "LEFT JOIN" to "INNER JOIN" immediately solves the issue and 18 seconds will bump to .2 seconds, HOWEVER, if either table has records the other doesn't, you will miss out on that result.

To keep the "LEFT" and move at lightning speed, you have to reduce to ONE "LIKE" via CONCAT.

My query was 19 seconds, and is now .03

CHANGE YOUR

FROM klanten k LEFT JOIN bestellingen b ON (k.klantId=b.klantId)  
WHERE (k.voornaam LIKE '%henk%' OR k.achternaam LIKE '%henk%' OR b.bestelId LIKE '%henk%')

TO

FROM klanten k LEFT JOIN bestellingen b ON (k.klantId=b.klantId)  
WHERE CONCAT(k.voornaam,' ',k.achternaam,' ',b.bestelId,' ') LIKE '%henk%'

Your now back to running ONE like, and instead of looking it up each time in each table, the concat gives it one item to check. This only works if you are looking for the same "like" in all tables.

Mysql query very slow when selecting from 2 tables

2 Answers2