I want to know when doing a SELECT query how is it executed. For Example, if there is 1000 records with product_price<10, 3000 records with is_available='Y' and the table contains 200,000 records:
SELECT product_code, product_description, product_price FROM products WHERE is_available='Y' AND product_price<10;
SELECT product_code, product_description, product_price FROM products WHERE product_price<10 AND is_available='Y';
Which one of the query will be faster? In query 1, will mysql sort the list for is_available='Y' then from the list find product_price<'10'? Please explain!
There should be no difference in quey execution. If there is an index on either column with good cardinality, then that index will probably be used to eliminate records first. If you are on mysql 5.0+ then multiple index may be used (merge index). After this happens each individual row will need to be examined, which will be expensive depending on the number or rows left after using the index.
MySQL provides EXPLAIN and EXPLAIN EXTENDED which prove real friends for questions like this. At some point I need to dig into the mysql source to gain a better understanding of what is going on. So, you can use like this to know that how the query will be executed:
EXPLAIN SELECT product_code, product_description, product_price FROM products
WHERE is_available='Y' AND product_price<10;