How mysql process where condition
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.