How mysql process where condition I have a query like

Select * from tbl1 

WHERE 
  ( price &gt; 10 
AND qty &gt; 200 
AND condition=1 
AND name like '%abc%') 


My question is how MySQL will process this? My concern is that I want to run query in this way that results should be reduced on basis of conditions. 

For example 

( price &gt; 10 AND qty &gt; 200 AND condition=1 And name like '%abc%') 


First, MySQL should filter records having price &gt; 10 and apply next condtion qty &gt; 200 on resultant dataset and at the end, it should put this condition AND name like '%abc%' on it.

How should I write query to achieve this?

Another question is: Does MySQL start reading conditions from start to end or from end to start?
How MySQL processes the query largely (and primarily) depends on the indexes on the table.

If no indexes exist, then of course a full table scan is required. Now, you are looking for a way to reduce the search space based on conditions. However:


Your query uses one "equals" condition
Your query uses two "range" conditions
It uses one LIKE clause with a prefix '%'


The LIKE clause, beginning with '%' negates any use of an index, unless it is a covering index, which is not the case in your query. This part of the clause negates any use of an index on the name column.

Well, you can use as many "equals" conditions as you like, where possible, but up to one range condition only, when using an index.

Any index you'll want to put on that table will start with the condition column, since it is the one column where you say condition = 1, which is an equality check. You are then left to choose any of the two columns on which you place a range condition. Choose the one which will most likely reduce more rows (leaving less rows in the search space).

So, you options are:


KEY (condition, qty)
KEY (condition, price)


So that MySQL will first match the condition column in the index, then follow up to the next column, whichever it is.

MySQL does not read conditions from start to end nor from end to start. It just notes down the various conditions, then tries, when possible, to find an index which satisfies them (or part of them). It then depends on the order of columns in the index -- not on the order by which the columns appear in the query.