mysql - Slow SQL query when grouping by two columns with self join -

i have table rating less 300k rows , sql query:

  select rt1.product_id id1, rt2.product_id id2, sum(1), sum(rt1.rate-rt2.rate) sum  rating rt1  join rating rt2 on rt1.user_id = rt2.user_id , rt1.product_id != rt2.product_id  group rt1.product_id, rt2.product_id limit 1 

the problem is.. it's slow. takes 36 secs execute limit 1, while need execute without limit. figured out, slowdown caused group by part. works fine while grouping 1 column no matter table rt1 or rt2. have tried indexes, have created indexes user_id, product_id, rate , (user_id, product_id).

explain doesn't tell me too.

 id     select_type     table   type    possible_keys   key     key_len     ref     rows    1   simple  rt1         primary,user_id,user_product    null    null    null    289700  using temporary; using filesort 1   simple  rt2     ref     primary,user_id,user_product    user_id     4   mgrshop.rt1.user_id     30  using 

i need execute once generate data, it's not important achieve optimal time, reasonable.

any ideas?

edit.

full table schema

create table if not exists `rating` (   `user_id` int(11) not null,   `product_id` int(11) not null,   `rate` int(11) not null,   primary key (`user_id`,`product_id`),   key `user_id` (`user_id`),   key `product_id` (`product_id`),   key `user_product` (`user_id`,`product_id`),   key `rate` (`rate`) ) engine=myisam default charset=utf8; 

your problem in join, and rt1.product_id != rt2.product_id. lets user has rated 100 products, user, query generate 99,000 rows before group by. each of 100 ratings, table gets joined 99 times.

what question trying answer query? depending on that, there may more efficient approaches. hard tell trying achieve here.