MySQL Forums
Forum List  »  Newbie

query performance
Posted by: HenLab
Date: August 10, 2004 10:04AM


we have an article table with 600000 data sets.

I think it's no problem to make a LIKE search on it with wildcards.

But see the following statement please:

SELECT artikel.*, AS temp_zone, kunden.kdnr AS temp_kdnr, CONCAT(kunden.firma_land, '-', kunden.firma_plz) AS temp_ort
FROM artikel, kunden, zonen
WHERE = artikel.zonen_id
AND = artikel.kunden_id
AND (artikel.artnr LIKE '%303%' OR LIKE '%303%')
AND artikel.kunden_id != '6'
AND artikel.anzahl > 0 AND artikel.preis_vk > 0
AND artikel.zonen_id IN (18, 12, 2, 1, 16, 19, 17, 14, 11, 10, 5, 15)
ORDER BY artikel.artnr LIMIT 0, 25

Ok, no problem but it takes about 10 seconds to get the result sets

On all important fields are indexes and the references to the other tables are no problem also the single conditions. The only problem are the OR combinded LIKE statements.

By removing the parenthesis the query takes less than 1 second.
Sure that kind of statement adulterates the result.

Here the shorten statement:

SELECT * FROM artikel
WHERE kunden_id != '6'
AND anzahl > 0
AND preis_vk > 0
AND zonen_id IN (18, 12, 2, 1, 16, 19, 17, 14, 11, 10, 5, 15)
AND artnr LIKE '%303%' OR LIKE '%303%'
ORDER BY artnr LIMIT 0, 25

Has anyone an idea to optimize the query or can say what possibilities we have to get faster results. Within the parenthesis we have up to 7 fields for a LIKE request.
At last the article table can have up to 2000000 entries.

Perhaps has somone other ideas.
perhaps without using a database,
perhaps an indexed static file and so on.
I think other also had this problem. There must be a solution.

We can give you an account for testing our database in real.
We need help.


Options: ReplyQuote

Written By
query performance
August 10, 2004 10:04AM
August 10, 2004 10:11AM
August 10, 2004 10:17AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.