Can this where clause be optimized?

Question

Can someone help me solve this problem? I have a question about the following feature; can I create a virtual column for it?

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
      )

Answer 1

Once the column in the WHERE clause is wrapped with a function (date in your case), the MySQL optimizer will not use the index.

Your query may be slightly 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's faster.