How to optimize this query using better date comparsion in SQL?

Question

Hello,

I have problem with this query, complexity of this query is not good, i use this query from long time and now this database have many rows to get selecting by this method. All index'es is added propertly. I searching some other method to optimize this query by date comparsion because this is bottleneck in this solution.

SELECT (...) FROM table 
WHERE (YEAR(row_add_date) * 10000 + 
       MONTH(row_add_date) * 100 + 
       DAYOFMONTH(row_add_date)) >= (var_0 * 10000 + var_1 * 100 + var_2) and
      (YEAR(row_add_date) * 10000 + 
       MONTH(row_add_date) * 100 + 
       DAYOFMONTH(row_add_date)) <= (var_3 * 10000 + var_4 * 100 + var_5) 

Can anyone help me? Greetings

Answer 1

Why are you breaking apart the date like that? Per-row functions do not scale well. It seems to me that the entire date section at the end can be replaced by:

where row_add_date = '2010-10-27'

Even if you want a range, you're still better of using the dates as they are.

---

Based on your edits to state that you're using variables, you should do the calculation on the right-hand side of the conditions. That's because this will be done once before the query start. With what you have, the calculation on the left side will be done once per row, a definite performance killer.

Answer 2

I'd suggest using built-in mysql date comparisons.

row_add_date <= '20101027' and row_add_date >= '20101027'

But note that this is a strange test in the first place: aren't just testing that the date is equal to October 27th, like this:

row_add_date = '20101027'

Answer 3

I'm going to guess that row_add_date is of type datetime. If so, you need turn 20101027 into a datetime, and compare the column to that.

In other words:

row_add_date >= firstDateTime and row_add_date <= secondDateTime