MySQL Forums
Forum List  »  Performance

Join/indexes question
Posted by: SOL MYR
Date: March 17, 2011 08:05AM

Hi,

Say I need to SELECT "people that are either Musicians themselves, or have a Musician kid".
Note I show only grownups (not kids, even if they triggered the selection of their parents).

My tables are PERSON and KID, each has a HOBBY column (with might be "music"), plus INDEX on it.
PERSON table:
- ID (primary key)
- HOBBY (with INDEX on it)
KID table:
- ID
- HOBBY (with INDEX on it)
- PARENT_ID (foreign key)

What's the best way to query, assuming I have millions of people, but musicians are rare?

1) I tried a naiive nested select, but it seems inefficient:
SELECT * from PERSON
WHERE PERSON.HOBBY='music' or EXISTS(select* from KID where PARENT_ID=PERSON.ID and HOBBY='music')

2) I tried to unite 2 different selects, and it's efficient but not very readable:
SELECT* from PERSON where HOBBY='music'
UNION
select PERSON.* from PERSON inner join KID on KID.PARENT_ID=PERSON.ID
where KID.HOBBY='music'

Is there a way to achieve efficiency, while still keeping it readable?
Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Join/indexes question
2266
March 17, 2011 08:05AM
651
March 19, 2011 12:16AM
704
March 20, 2011 08:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.