如何提高具有多个Where条件和Order By的查询的速度？

Question

I have stored all the Permutations and combinations of all the attributes necessary to create a product based on color,weight and quality and stored it in attribute table.

In program I loop on the data from this table and create Select sql to query another table which contains Product Price based on the attributes and I store it in third table.

I have suppose 7000 records in attribute table and 2 Lakh records in Price table. So the program loops to 7000 records and Each select SQL queries 2Lakh records.

Select SQL contains Multiple Where Clauses and a Order by on Price to get the lowest price.

My question is how can I reduce the query execution time.

Example :-

Attribute table

sno  color      Quality  Weight
1      blue     Good       3kg
2      red      Fair        1kg   
3      Yellow   Excellent   1.5Kg

Price table

sno     color     Quality    Weight(in kgs)    Market Price  Our Price 
1      sky blue    Good       4                 $400           $360
2      orange red  Excellent  2                 $500           $450

Price table For Red I store - Crimson Red,Orange Red etc.

But Attribute I have Red bcoz Red is Prime color and in market u don't get perfect Red. So I give Product close to Red and cheaper in that Red group.

select * from tbl_price 
where color IN {Array [tbl data Entry i.e. RED ]    gives-> ( "Crimson Red", "Orange Red","Carrot Red" )}
AND Quality IN {Array [tbl data Entry i.e. Good ] gives-> ("Above Average","Medium","Not Bad" )}
AND Weight >= {tbl Entry of Weight}
Order by OurPrice ASC
LIMIT 1,1;

Answer 1

quote: "the program loops to 7000 records and Each select SQL queries 2Lakh records"

This makes it sound like you are processing RBAR (row-by-agonizing-row), and running 7,000 queries.

It is usually much more efficient (in SQL) to process the rows as a set, and run a single query that returns the resultset you want.

There's overhead associated with issuing a SQL statement (client sending the SQL text, server parsing the text, performing a syntax check, performing semantics check, choosing an execution plan, and then executing the plan, preparing a resultset, returning results to the client.)

With thousands of query executions, statement processing overhead can be a significant portion of the total elapsed time, no matter how efficiently you get the actual query to run.

(It's entirely possible that I've misinterpreted what you were saying.)

Curiously, your example query shows a LIMIT 1,1 clause. That's "skipping" the first row in the resultset. That seems a bit odd, seeing as you said you wanted to get the "lowest" price, and not the second lowest price.

With MySQL, you can use EXPLAIN to see the execution plan; that may give insight to performance issues. Sometimes, adding an appropriate index may significantly improve performance. But, in this case, if the query you are trying to improve is searching a table that contains two rows, it's very unlikely any index will make any difference in performance.

Bottom line, to improve performance, if you are executing 7,000 queries, re-work the design so that you process all 7,000 rows as a set. It's likely that a single query that returns the results would be more efficient.