MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / SQL Statements / Select Statement / Question No: 192

How the select query is executed?

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!

Answer No: 192

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;

Related MySQL FAQs to the Above FAQ

How-to-use-SELECT-statement-to-retrieve-data How to use SELECT statement to retrieve data?

How-does-DISTINCT-work-in-MySQL How does DISTINCT work in MySQL?

How-to-use-ORDER-BY-to-sort-query-results How to use ORDER BY to sort query results?

How-to-use-LIMIT-clause-in-MySQL How to use LIMIT clause in MySQL?

How-to-get-aggregate-results-in-MySQL How to get aggregate results in MySQL?

How-can-I-limit-the-number-of-rows-I-want-to-fetch-from-my-MySQL-table How can I limit the number of rows I want to fetch from my MySQL table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.