CODEWITHSUNDEEP
mysqlsqldatabase
Mircea
Mircea

Reputation: 35

How can this SQL be improved? (MySQL database)

SELECT * 
FROM inventory 
WHERE inventory.zip IN (93650,93750,93765,93729,93710,93740,93711,93720,93704,93741,93705,93755,93791,93790,93794,93793,93726,93792) AND 
      inventory.price >= 1000 AND 
      inventory.year BETWEEN 1977 AND 2011 
ORDER BY price ASC LIMIT 0, 25

The database is MySQL InnoDB tables (switching to MyISAM to see how it works). The table has about 500,000 records and the fields searched are indexed. It takes very long until I get the results and sometimes I get Internal Server Error.

How can the query be improved?

Upvotes: 0

Views: 85

Answers (1)

pap
pap

Reputation: 27614

Study the execution plan for the query. In MySql this is done using the explain command

EXPLAIN SELECT * 
FROM inventory 
WHERE inventory.zip IN (93650,93750,93765,93729,93710,93740,93711,93720,93704,93741,93705,93755,93791,93790,93794,93793,93726,93792) AND 
      inventory.price >= 1000 AND 
      inventory.year BETWEEN 1977 AND 2011 
ORDER BY price ASC LIMIT 0, 25

This should give you the execution plan. With that, you can see if it's properly using indexes or if one of your conditions is provoking a full table scan. If it does, either you need another index or you might need to modify the query itself.

Upvotes: 3

Related Questions