search - MySQL - searching a self join and ranges or data -

i'm tasked local community center build 'newlywed' type game in time valentines day, no rush!

so, we've got 50 odd couples know each other quite going asked 100 questions before time. each question has users response , range @ allow margin of error (this range quota limited). , can select think partners answer be, same range margin of error.

eg (i'll play round me , gf):

question: fruit? quite fussy fruit i'll put low score out of 100.. 20. like, love , think gf might think put higher answer, margin of error i'll allow going 30. think loves fruit , put @ least 90.. enjoys alot of foods may rank lower, i'll give margin of 20.

ok, repeat process 100 questions , 50 couples.

i'm left table this:

u_a = user answer

u_l = user margin of error level

p_a = partner answer

p_l = partner margin of error level

create table if not exists `large` ( `id_user` int(11) not null, `id_q` int(11) not null, `u_a` int(11) not null, `u_l` int(11) not null, `p_a` int(11) not null, `p_l` int(11) not null, key `id_user` (`id_user`,`id_q`) ) engine=innodb default charset=latin1 comment='stackoverflow test'; 

so row in previous example:

(1, 1, 20, 30, 90, 20)

my mission search users see best matches out of 50.. (and hope couples together!).

i'll want search db users answer partner matches answer, every user.

here's i've got far (note i've commented out code, that's cause i'm trying 2 ways, not sure what's best):

select match.id_user, count(*) count `large` `match` inner join `large` `me` on me.id_q = match.id_q me.id_user = 1 , match.id_user != 1 , greatest(abs(me.p_a - match.u_a), 0) <= me.p_l , greatest(abs(match.p_a - me.u_a), 0) <= match.p_l   #match.u_a between greatest(me.p_a - me.p_l, 0) , (me.p_a + me.p_l) #and #me.u_a between greatest(match.p_a - match.p_l, 0) , (match.p_a + match.p_l)  group match.id_user  order count desc 

my question today :

this query takes ages! i'd during game , allow users chance change answers on night , instant results, has quick. i'm looking @ 40 seconds when looking matches me (user 1).

i'm reading db engines , indexing make sure i'm doing can... suggestions welcome!

cheers , phew!

your query shouldn't taking 40 seconds on smallish data set. best way know going on use explain before query.

however, suspect problem condition on me. mysql engine might creating possible combinations users , then filtering out. can test modifying code:

from `large` `match` inner join      `large` `me`      on me.id_q = match.id_q me.id_user = 1 ,       match.id_user != 1 , . . . . 

to:

from `large` `match` inner join      (select me.*       `large` `me`       me.id_user = 1      ) me      on me.id_q = match.id_q match.id_user != 1 , . . . . 

in addition, following indexes might query: large(id_user, id_q) , large(id_q).