Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
3  
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
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.