Adding an index to a MySQL table

Question

The MySQL documentation isn't very clear on this. I want to add an index to an existing table. The table is a user table with the login id and password and I want to create an index for this to optimize logging in.

This is how I thought I would try it:

mysql> ALTER TABLE `users` ADD INDEX(`name`,`password`);

This created:

mysql> show index from karmerd.users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+   
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+    
| users |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |     
| users |          1 | name     |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |     
| users |          1 | name     |            2 | password    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |     
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Did this achieve what I was trying to do? (Optimize logging in?) Previously I only had a primary key on an a field called 'id'.

Answer 1

Yes, this achieved creating an index. The index is named "name" (if you don't give the index a name, it tries to use the first column you specify in the index). The index is a composite of two columns: name in position 1, and password in position 2.

As for whether or not this will optimize logging it, that depends on how your queries may or may not use the index. You should also learn about how to analyze queries with EXPLAIN.

---

You should also read more about storing passwords.

Here's a good blog on the subject: "You're Probably Storing Passwords Incorrectly"

Answer 2

Unless usernames are not unique in your application, I don't think that's really make sense to index "password"; the index will be bigger and inserts will be slower for no added value.