How mysql process where condition

Question

I have a query like

Select * from tbl1 

WHERE 
  ( price > 10 
AND qty > 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 > 10 AND qty > 200 AND condition=1 And name like '%abc%') 

First, MySQL should filter records having price > 10 and apply next condtion qty > 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?

Answer 1

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.

Answer 2

If you do not have index on table, then mysql will check every row for all conditions and that will be slow. If you add correct index, you can reduce number of rows that it has to check. But you should avoid

like '%abc%'

search - it is unable to use any index. Use full text search or external search engines like Sorl, Elasticsearch or Sphinx for extensive text search.