In SQL, does the WHERE clause order have any effect?

Question

I have a table in my DB something like this:

---------------------------------------------------------- | event_id | date | start_time | end_time | duration | ---------------------------------------------------------- | 1 | 2011-05-13 | 01:00:00 | 04:00:00 | 10800 | | 2 | 2011-05-12 | 17:00:00 | 01:00:00 | 28800 | | 3 | 2011-05-11 | 11:00:00 | 14:00:00 | 10800 | ---------------------------------------------------------- 

In this example, the data does not give an absolutely accurate picture, usually events cover every hour of every day. The date always refers to start_time, since end_time can sometimes be the next day. Duration in seconds.

 SELECT * FROM event_schedules WHERE ( date = CURDATE() //today OR date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) //yesterday ) // and ended before now() AND DATE_ADD(CONCAT(date, ' ', start_time), INTERVAL duration SECOND) < NOW() ORDER BY CONCAT(date, ' ', start_time) DESC LIMIT 1 

I have a sentence, an OR'ed clause in brackets, which is optional. I was hoping this could improve the query time by first filtering out any “events” that don't fire today or yesterday. The only way to find the last “event” is to order records first. By adding this extra sentence, am I actually reducing the list of records that need to be ordered? If so, I cannot imagine the optimizer capable of doing this optimization, most of the other issues like this talk about the optimizer.

Answer 1

Be careful when adding filters to the WHERE clause to improve performance. Although it can reduce the total number of rows to look for, the actual filter itself can cause a higher cost if it filters a ton of records and does not use an index. In your case, if the column date is indexed, you are likely to get better performance because it can use the index in the OR part, where it cannot in other parts, because it is called a function. Also, can you have future dates? If not, why don't you change OR to

 date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) 

Answer 2

The order of the where clause affects the way the SQL engine retrieves the results.

Many of them have the ability to see what the engine is doing with the request. If you are using sqlserver, look at "show an evaluation plan for execution" in your client tool. Some have an “explanation” type verb that can be used to show how the engine handles the request.

Answer 3

Well, the optimizer in the query engine is a big part of any query performance or the relative performance of two equivalent statements.

You did not tell us if you ran the request with and without extra space. There may be a difference in performance, it cannot be.

I assume that LIMIT has a lot to do with this. The engine knows that this is a one-and-one operation. Without WHERE, sorting is an NlogN operation, which in this special case can be made linear with a simple date scan to find the most recent.

With WHERE you actually increase the number of steps that it must complete; either it should completely arrange the table (NlogN) and then scan this list for the first entry that matches the WHERE clause (linear worst case, constant option), or it should filter WHERE (linear), then scan those records again to find the maximum date (again linear). Depending on what is faster, they are slower than a single linear scan of the list for the most recent date.