I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below:
SELECT t1.`ARTREF1`
FROM `lijnen` AS t3
INNER JOIN
(`kortingen` AS t2, `raiproduct` AS t1)
ON (t1.LEV=t3.LEV
AND t2.LEV=t3.LEV
AND t1.KCP1=t3.SPLC
AND t2.SPLC=t3.SPLC
AND t1.LEV=t2.LEV
AND t2.SPLC=t1.KCP1
AND t1.`ARTREF1`='".$xartc."'
AND t2.ALDOC='".$supcodec."'
AND t3.BVNR='".$bvnr."'
)
How it works:
I get through SOAP webservice an XML document. In the XML document I get the article number and supplier number and I query my database to see if the article exists.
It takes nearly 5 to 8 seconds to show the results on screen.
I use indexes on the rows which I search. Every day I purge empty fields to optimize my database How can I increase the speed of the query?
Some database and hardware information:
raiproduct
: 1.500.000 rowslijnen
: 4100 rowskortingen
: 1200 rows- CPU: 4 cores
- Processors: Intel 64bit hexacore (6 cores) 2.5 Ghz
- RAM: 4096 MB
- SWAP RAM: 1024 MB
I hope someone can help me with this problem.