Re: query speed joining tables

Christopher Smith wrote:
>my mistakes, zips_max should be zips_300.>and>in my zip code table there are 120 million rows, example of the 
records >are>>origin           destination>===================>>90210           90222>90210           90234>90210
   96753
 


1.try to create index on both fields on zips_300 - origin and destination
zips_300_ind(origin,destination)
2.if you have only unique pairs in zips_300, this query should noticable 
speed up you example:

select userid    from    user_login UL    join user_details_p UD using (userid)    join user_match_details UM using
(userid)   join zips_300 Z on (Z.destination=UM.zipcode and Z.origin='90210')
 
where    UD.gender ='W' AND    UD.seekgender ='M' AND    UD.age between 18 and 50 and    UMD.min_age <= 30 AND
UMD.max_age>= 30 AND    UD.ethnictype = 'Caucasian (White)' AND    strpos(UMD.ethnicity,'Asian') !=0    order by
user_login.last_logindesc;
 

Next step to speed up your query is answering such question:
- How many values do I get if I ask one question.
Example:
gender='W' - 50% rows
seekgender='M' - 50% rows
ethnictype='Caucasian (White)' - 5%

Start indexing your tables on smallest values - in this situation - 
ethnictype. Consider using multi-column indexes.

Regards,
Tomasz Myrta