Search code examples
mysqlsqlquery-optimization

Is it possible to optimize this where clause?

Can anyone help me with this? I have doubts about the below function; can I create a virtual column for this?

 select as1.col,as1.col2,as1.col3 from 
 analytics.adjusted_sale_velocity
 where 
      date(as1.created_datetime)=(
        select 
          max(
            date(created_datetime)
          )  
        from 
          analytics.adjusted_sale_velocity
      )
Solution

  • MySQL optimizer won't use an index once a column in the WHERE clause is wrapped with a function, date in your case.

    Your query might be written a little different:

    select as1.col,
       as1.col2,
       as1.col3 
from  adjusted_sale_velocity a
inner join ( select  max(created_datetime) as created_datetime   
             from adjusted_sale_velocity
            ) as max_dt on left(a.created_datetime,10) = left(max_dt.created_datetime,10) ;

    Try and let me know if it is faster.