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/[EMAIL PROTECTED]
