Re: Tuning - Table Design and Query
Posted by: Brian Papantonio
Date: July 12, 2007 11:29AM

Composite indexes make sense for that sort of thing, but in this case I see no use for it. You should have already created a primary key or at least a unique index on the username column; this will also increase performance when querying that column.

You want to query off of username first because it will only return 1 row. Querying off of "confirmed" first would be bad, even if there was an index, because ~50% of the rows would be returned by the index, and then it must scan through those returned rows to find the username.

In general, do not put indexes on columns that have very few possible values (e.g., BIT fields).

So assuming you have a case-insensitive unique index on username, that's all you need for max performance. If you made username a primary key then it automatically adds a unique index.

MySQL 5.0 Certified DBA
MySQL 5.0 Certified Developer

Options: ReplyQuote

Written By
Re: Tuning - Table Design and Query
July 12, 2007 11:29AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.