[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