[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:
   other things...
table titles_genres:
   unique key (title_id, genre_id)
   key (genre_id, title_id)
table genres:

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


More information about the thelist mailing list