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 rows
lijnen: 4100 rows
kortingen: 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.