Hi Dan, "Dan Buettner" <[EMAIL PROTECTED]> writes: > The answer is - "it depends". >
Thanks for your suggestions. > A multi-column index can be helpful for performance over single-column > - or it can do you no good at all, depending on how you build it and > how you use it. > > MySQL currently uses (at most) one index per instance of a table per > query. This means that adding several single-column indexes will not > help speed up an existing query. On the other hand, several > single-column indexes may be just what you need if you query a number > of different ways. > > Multi-column indexes are built in the order you specify the columns, > and can only be used if you are specifying some or all of the columns > in the same order you specified when you built the index. > > In other words, if you have TABLE with columns A B C and D, and you > create a multi-column index on (A, B, C), your index will be helpful > in these situations (order by most helpful to least helpful): > > In my experience, multi-column indexes make for wicked fast searches > when you have queries that will make use of them. There's nothing > stopping you from creating multi-column as well as single-column - > there's some overhead in terms of disk space and speed of > insert/update/delete but it's technically possible. > In my case, right now always all the columns will be present, so I will go with multi-column indexes as you suggested. I was just being circumspect about my usage of them, as from my limited experience I have not seen many databases using them. Thanks once again, for the nice didactic explanation. Cheers, -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com Read my blog at: http://cuttingtheredtape.blogspot.com/ ,---- | "O thou my friend! The prosperity of Crime is like unto the lightning, | whose traitorous brilliancies embellish the atmosphere but for an | instant, in order to hurl into death's very depths the luckless one | they have dazzled." -- Marquis de Sade `---- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
