After some discussion in a separate thread, I've been trying
to get a better understanding of the workings of multiple-column
indexes, and think I'm still missing the point. I understand
indexing (last_name, first_name) in that order if you'd always
be searching last names and only be searching first names in
combination.
What I'm trying to understand is how you would set up these
indexes when you'd always be doing joins with another table.
Suppose you have The Canonical CD Database, and you have a
table "songs" with fields "song_id", "album_id", "song_title",
and "song_length". Suppose you're often doing searches of
song_title or (for some reason) song_length, and that any time
you'd do such a search, you'd _always_ be joining it to the
"album" table.
It would seem that you'd want at least two multiple-indexes in
the "song" table, one of them including "song_title" and
"album_id", the other including "song_length" and "album_id".
Is this correct? Do you need "song_id" (which would be a
primary key on that table) in there too? What order should
the indexes be in?
If every search for song_title or song_length must be joined
against the album table, it's not clear which should be the
first named column in this index. The experiments I've done
so far have been inconclusive, and I don't think I'm understanding
the process in the first place.
Thanks very much.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives:
http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/my***********...ie.nctu.edu.tw 
