Question regarding MySQL indices and their functionality

Say I have an ordinary table in my db like so

----------------------------
| id | username | password |
----------------------------
| 24 | blah     | blah     |
----------------------------

A primary key is assigned开发者_开发百科 to the id column. Now when I run a Mysql query like this:

SELECT id FROM table WHERE username = 'blah' LIMIT 1

Does that primary key index even help? If I am telling it to match usernames, then shouldn't the username column be indexed instead?

Thanks for your time

In general, when you create an index, you do it based on the expectation of what will be queried. So if you know in advance that you will always, or often, query by username, then you should create an index on username. If you are using the id column as an identifier or foreign key from other tables, then create an index on the id column.

To answer your question directly -- yes, index should be on username if you query by username, and if you never query by id.

No in that situation the primary key index wouldn't help. However, it is useful for many other situations. You can add a secondary index to the username field like this:

alter table mytable add index (username);

The reason to generally add an integer field and assign it as the primary key to any particular table is so that field may be used as a foreign key in other tables.

In this query it won't help. An index on username would help in this one.

But the index will significantly speed up wheres and joins on id.

You are right. Indexing only increases performance when there is a search, joins or aggregate function on the column.

Nope, not in this case.

If you run "EXPLAIN" on the query, you'll see that it wouldn't be used.

EXPLAIN SELECT id FROM table WHERE username = 'blah' LIMIT 1;

It depends upon your query as to which index, if any, will be used. An index on the username would likely speed up your query.