mysql - Is this query can be optimized? -

i have mysql database table has following columns..

id dealerid month_year total_price total_count vin_prefix 

and table has 20 million records, want sum(total_price) of cars sold dealer id # 02 (it's pretty simple)

however, want sum of other dealers have sold cars same vin_prefix dealer # 02

so wrote query...

select d.dealerid      `dealer_monthly_report` d      d.dealerid <> 2          , d.vin_prefix in (select distinct(d2.vin_prefix)                                   `dealer_monthly_report` d2                                     d2.dealerid = 2)      group d.dealerid 

but can optimize query in anyway ? indexes have been used on dealerid, vin_prefix , month_year seperately.

thanks !

just tried query , gordon's query 1.000.000 records. adding index dealer_monthly_report(vin_prefix, dealerid) suggested query seems bit faster gordon's 1 (about 1-2 seconds records).

edit: corrected code temporary table

i suggest extend first solution gordon follows:

create temporary table vin_prefixes_dealerid_2     select distinct(d.vin_prefix) vin_prefix     `dealer_monthly_report` d     d.dealerid = 2 ;  select d.dealerid, sum(total_price) `dealer_monthly_report` d d.dealerid <> 2 , exists (     select 1      `vin_prefixes_dealerid_2` d2     d.vin_prefix = d2.vin_prefix ) group d.dealerid; 

this reduced query time initial 18-20 seconds 6-7 seconds (for 1.000.000 records)

edit: corrected alternative approach

alternative approach might be:

create temporary table vin_prefixes_dealerid_2     select distinct(d.vin_prefix) vin_prefix     `dealer_monthly_report` d     d.dealerid = 2 ;  select d.dealerid, sum(d.total_price) `dealer_monthly_report` d join `vin_prefixes_dealerid_2` d2 on d.vin_prefix = d2.vin_prefix d.dealerid <> 2 group d.dealerid; 

this reduced time processing 0.91 (!!!) seconds