Search code examples
sqlmysqloptimizationdate-comparison

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


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


Solution

  • 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'