Using "IN"!

[cherni99]

Hi all,

 

I have the following DB structure with the following data...

 

Person A lives in Holland (stored in "person" table) and would like to visit Spain,Italy,Portugal (stored in "places" table)

Person B lives in Spain (stored in "person" table) and would like to visit Holland,Germany,Sweden (stored in "places" table)

 

I am running the query below:

 

select  count(*)

from    person pe1,

            places pl1,

            person pe2,

            places pl2

where  pe1.userid_db = pl1.userid_db

and      pe2.userid_db = pl2.userid_db

and      pe1.country_db in (pl2.countries_of_interest_db)

and      pe2.country_db in (pl1.countries_of_interest_db)

 

For now, there are approx 300 person records to scan and match. I realise that the "IN" is not very efficient so my question is "is there a way to optimise this query??"

 

Version: MySQL client version: 4.1.15

 

Thanks for your help!

C

 

[cherni99]

OK - I read the rules...

 

Another thing to consider, there will be a join on person so the query will change to:

 

select  count(*)

from    person pe1,

            places pl1,

            person pe2,

            places pl2

where  pe1.userid_db = 1234567890  /* I will link the user id directly to pl1 but I'll leave this til the main reason of this post is discussed */

and      pe1.userid_db = pl1.userid_db

and      pe2.userid_db = pl2.userid_db

and      pe1.country_db in (pl2.countries_of_interest_db)

and      pe2.country_db in (pl1.countries_of_interest_db)

 

The query works...I'm just trying to make it more efficient.

 

The EXPLAIN...

id select_type table type possible_keys   key   key_len ref     rows Extra

1 SIMPLE         pl1         ALL         NULL           NULL        NULL         NULL    257 Using where

1 SIMPLE         pl2         ALL         NULL           NULL        NULL         NULL    257

1 SIMPLE         pe1         ALL         NULL           NULL        NULL         NULL    264 Using where

1 SIMPLE         pe2         ALL         NULL           NULL        NULL         NULL    264 Using where

[fenway]

Yikes -- how come you don't have any indexes?

[cherni99]

Haha - I know...I know...unfortunately I didnt create the tables and I have little control over them (for now)....any ideas on the query (but thanks for the comment anyway!)???

[fenway]

You can't fix the query without the index.