[thelist] basic SQL many-many optimization
SBeam
sbeam at syxyz.net
Wed Jul 16 09:23:32 CDT 2003
Hello all -
I have a searchable database of Video/DVD titles which are categorized by
genre. A title can have one or more genres and of course many titles can
belong to the same genre. The following schema is in play:
_____________
table titles:
id
title_name
other things...
_____________
table titles_genres:
title_id
genre_id
unique key (title_id, genre_id)
key (genre_id, title_id)
_____________
table genres:
id
genre_name
So, when a search for titles returns many results, what is the most optimal
way of getting the list of genres for each title? It is working now as I do:
SELECT g.id, genre_name FROM titles_genres tg, genres g
WHERE tg.title_id = $title_id AND g.id = tg.genre_id
for _each_ row returned by the title search query. With 100s of results at
times this seems ridiculous. This is a basic many-many relationship but I
can't seem to find a definitive answer.
using mySQL 3.23 but would also like to know what the best practice is in
general, ie if subselects or stored proc is the answer
-S
More information about the thelist
mailing list