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 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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments