Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below:

FROM `lijnen` AS t3 
    (`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.

share|improve this question
Try doing EXPLAIN on this query and look what it really does. – Agares Sep 18 '13 at 19:15
Can you show us an create table structure and an explain. this information about an query wont help that much – Raymond Nijland Sep 18 '13 at 23:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.