mysql - Need a simple solutin to slow query -

i have following query..

select avg(h.price) `car_history_optimized` h left join vin_data vd on (concat(substr(h.vin,1,8),'_',substr(h.vin,10,3))=vd.prefix) h.date >='2015-01-01'   , h.date <='2015-04-01'   , h.dealer_id <> 2389   , vd.prefix in     (select concat(substr(h.vin,1,8),'_',substr(h.vin,10,3))      `car_history_optimized` h      left join vin_data vd on (concat(substr(h.vin,1,8),'_',substr(h.vin,10,3))=vd.prefix)      h.date >='2015-03-01'        , h.date <='2015-04-01'        , h.dealer_id =2389) 

it finds average market value of car sold within last 3 months else other (2389) car have same make, model sold (2389)

can above query optimized ? it's taking 2 minutes run 11 million records..

thanks

how use particular "prefix"? if often, direct toward indexing 'virtual' column.

otherwise, need

index(date)             -- outer query index(dealer_id, date)  -- subquery 

then exists suggested, or use left join ... ... null.

is date date? or datetime? may including day. suggest pattern:

where date >= '2015-01-01'   , date  < '2015-01-01' + interval 3 month