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.*, zonen.name AS temp_zone, kunden.kdnr AS temp_kdnr, CONCAT(kunden.firma_land, '-', kunden.firma_plz) AS temp_ort
FROM artikel, kunden, zonen
WHERE zonen.id = artikel.zonen_id
AND kunden.id = artikel.kunden_id
AND (artikel.artnr LIKE '%303%' OR artikel.name 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 artikel.name 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.