On Tue, Aug 26, 2003 at 09:26:55PM -0500, Matt W wrote: > Hi Jesse, > > ----- Original Message ----- > From: "Jesse Sheidlower" > > > 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? > > You wouldn't necessarily want indexes on (song_title, album_id) -- in > that order -- and/or (song_length, album_id). This reason for this is > because if any other columns from the song table are involved in the > query (in the select list or in the WHERE), MySQL will have to hit the > data file for those columns anyway, and album_id as the second column in > the index won't be used -- just the first column -- song_title or > song_length -- if you're searching on them. However, if only the 2 > columns in the index are involved in the query (searching on title or > length and join with album_id), then having album_id in the index would > be benficial because no seek to the data file is needed. This can be > verified by seeing if EXPLAIN says "Using index" for the song table.
[...] > > 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. > > The indexes would be: > > (song_title, album_id) > (song_length, album_id) > > If the order was reversed (e.g. album_id was first in the index), the > index couldn't be used for searching. > > And like I said above, if other columns besides the 2 in the index are > involved in the query, album_id isn't used anyway. In that case, just > index title and length separately for searching: > > (song_title) > (song_length) > > Of course, if you included ALL columns that will be used in queries in > each index (with title or length as the first column in each), then it > wouldn't have to go to the data file and EXPLAIN would say "Using > index." But this doesn't usually give much speed improvement and is a > waste of space. Just letting you know. :-) Well, if speed rather than space is my main concern, _and_ I can't predict what the searches will be--i.e. it's quite possible that some searches will be only song_title, some will be only song_length, and some will be both, and some will involve other combinations of fields not shown in this made-up example--then what? Do I have several multiple-column indexes, each with (song_title, album_id) or whatever for each field, along with single-column indexes for everything (even though every query would be joined on album_id)? Or do I need to have three- or more-column indexes for all the potential groups that might be searched? (song_title, song_length, album_id), (song_title, song_something_else, album_id), etc.? Thanks very much. Best, Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
