1

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.

2
  • 3
    Try doing EXPLAIN on this query and look what it really does.
    – Agares
    Sep 18, 2013 at 19:15
  • Can you show us an create table structure and an explain. this information about an query wont help that much Sep 18, 2013 at 23:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.