In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

Posted on

Question :

I have a query that doesn’t use any indexes:

SELECT 32,
       guid,
       1,
       1,
       1,
       0,
       5
FROM   test
WHERE  level >= 20
       AND ( ( fun_GetIndexValue(data, 354) >> 16 ) + 
             ( fun_GetIndexValue(data, 355) >> 16 ) + 
             ( fun_GetIndexValue(data, 356) >> 16 ) + 
             ( fun_GetIndexValue(data, 357) >> 16 ) + 
             ( fun_GetIndexValue(data, 358) >> 16 ) + 
             ( fun_GetIndexValue(data, 359) >> 16 ) ) >= 1; 

The level column has only about 80-90 distinct values, the table test has about million rows, and the data column is passed to the function, so I think the query can not use any indexes. But I found that if I put the level condition in the end, the query performs slower. Why is that?

Answer :

Maybe because the results from the first “form” of the query are already cached ?
As lyxing said, post the execution plan. It will be interesting to see how the optimizer chooses the execution plan in both cases.

Leave a Reply

Your email address will not be published. Required fields are marked *