Surendra - The answer is - "it depends".
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): SELECT D FROM TABLE WHERE A = 1 AND B = 2 AND C = 3 SELECT D FROM TABLE WHERE A = 1 AND B = 2 SELECT D FROM TABLE WHERE A = 1 You could also do SELECT D FROM TABLE WHERE A = 1 AND C = 3 but only the very first part of the index, built on A, will be used. The C part will not be searched by index. If you do SELECT D FROM TABLE WHERE B = 2 AND C = 3 SELECT D FROM TABLE WHERE C = 3 the index will not be used at all since the columns are not the first column specified in the index creation. 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. Hope this helps. Dan On 9/20/06, Surendra Singhi <[EMAIL PROTECTED]> wrote:
Hi, I have 4-5 different types of models which can be tagged. So, in the taggings table should I declare a multicolumn index on [taggable_type, taggable_id] or a single column index on [taggable_id]. What are the pros and cons of either, and which one is preferred? If multicolumn, then in what order, i.e., [taggable_type, taggable_id] or should it be [taggable_id, taggable_type]? I have another table for tracking views for different objects, and this time there are 3 columns [viewable_type, viewable_id, user_id], so should this one be a 3 column index? Please suggest something. Thanks in advance. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com Read my blog at: http://cuttingtheredtape.blogspot.com/ ,---- | Great wits are sure to madness near allied, | And thin partitions do their bounds divide. | | (John Dryden, Absalom and Achitophel, 1681) `---- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
