Can the order of criteria in a WHERE clause affect performance in MySQL?

Question

Given the following query:

select .................... from ...................... where ( lower(c01) like ? or lower(c02) like ? or lower(c5x3_.c01) like ? or lower(5x3_.c02) like ? ) and( ( lower(c03) like ? or lower(c04) like ? or lower(c5x3_.c03) like ? or lower(5x3_.c04) like ? ) /* ...Multiple further LIKEs... */ and status=1; /* status is a non-nullable value whose values can only be 1 or 2 */ 

The first 10 or 12 LIKE restrictions on different templates - I know that this can be slow, so first put a condition criterion, i.e.

 status=1; and(other LIKEs) 

My question is: can there be an increase in performance when applying simpler restrictions (for example, in this case, the compared int value cannot be a zero value and has only two possible values)? Or will the performance be the same if you first put LIKE , and a status check appears later?

Suppose there is no index for any column to simplify the question.

Answer 1

(part of the answer, part asks for more information ...)

It doesn't matter if you put status=1 first or last. On the other hand, the order of ANDed things in WHERE inconsequential.

LOWER(x) LIKE '...' much less efficient than setting sort x to collapse to a register, for example utf8_general_ci ( ci means "case insensitive"). When matching ci you only need to do x LIKE '...' .

But it will not speed up much. You have a lot of ORs ; they are inefficient because the index is usually useless, so a full table scan is required.

Often, OR can be converted to UNION to be able to use indexes. But probably not in your case.

Please provide SHOW CREATE TABLE . Hmmm ... A sample query makes you wonder if you have many identical tables. If so, perhaps we can move on to this as an inefficient way to design the circuit. So, tell us if c01 and c03 (etc.) look the same.

What values ​​will be placed in LIKEs ? The words? The numbers? Custom strings? Wildcards? Leading wildcards? Not a presenter? If "words", then FULLTEXT may be useful. But ... We will need to carefully examine the long (?) List of tables involved.

Answer 2

The order in which the conditions are placed may matter due to a MySQL short circuit. Here is an attempt to prove it:

http://rextester.com/HJGN96158

The following queries are executed here:

 SELECT myint FROM mytable WHERE myint >= 3 AND myslowfunction('query #1', myint) = 1; SELECT myint FROM mytable WHERE myslowfunction('query #2', myint) = 1 AND myint >= 3; 

The only difference between them is the order of the operands in the AND condition.

myslowfunction intentionally sleeps for a second and has the side effect of adding an entry to the log table every time it starts. Here are the results of what is recorded during the execution of the above two queries:

 myslowfunction called for query #1 with value 3 myslowfunction called for query #1 with value 4 myslowfunction called for query #2 with value 1 myslowfunction called for query #2 with value 2 myslowfunction called for query #2 with value 3 myslowfunction called for query #2 with value 4 

The above shows that a slow function is executed more times when it appears on the left side of the AND condition, when the other operand is not always false (due to a short circuit).

From this, I would advise putting the status = 1 check before checking LIKE as (pointers to the side), most likely, it will be evaluated faster.

Answer 3

- is it the same as before, and status criteria later?

You can find the answer yourself by running EXPLAIN against two versions of your query. For instance.

 EXPLAIN select ... from ... where 

Most likely, you will get two exactly the same execution plans, which means that the two statements are equivalent, because part of the status should be removed by optimizing the elimination of dead code .

Answer 4

SQL is a declarative language , you don’t care what “what to do” , so you don’t have to worry about “how” your query will be executed, the DBMS will take care of this and choose the best execution plan , so the short answer is that there is no difference in order.

Answer 5

First you have to set the condition with the least power. This filters the number of rows to view a smaller number.