phpMyAdmin / MySQL Indexes

[johnsmith153]

If I had a table with 15 fields (so it is worth creating an index) and I currently have an auto-incremented 'uid' field as the Primary Key, which of the fields below (if any) would I create an index on?

 

I would like an answer so I can test my knowledge of indexes.

 

(1) 15 fields total, some of which are Name, Username, Password, uid (primary key) etc. (index would be worth creating as lots of other fields)

 

(2) I regularly do:

SELECT Username FROM table WHERE uid='$_POST["username"]' && Password='$_POST["Password"]'

 

So what index should I create?

 

This is just a test. If it was a users' table you probably wouldn't have 15 fields etc.

 

I would normally escape data and structure the SQL query fully of course.

 

Please don't guess at the answer. I would rather have no answers than wrong ones. Thanks anyway.

[Mchl]

You should create such indexes, that will bring benefits for your queries. There's no point creating an index for each field in a table, because many of them will never be used. In your case if the table is InnoDB, then a multi-column index created on (Username,Password) might yield really good results. Also a multi-column index on (uid, Password) is worth trying out.