Adding Index to MySQL Table
MySQL documentation is not very clear. I want to add an index to an existing table. The table is a user table with a login id and password and I want to create an index on this to optimize the login.
This is how I thought I would try:
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 your login?) Before, I only had a primary key in a field named "id".
source to share
Yes, this led to the creation of the index. The index is called " name
" (if you do not specify an index name, it tries to use the first column you specified in the index). The index is a composition of two columns: name
at position 1 and password
at position 2.
As for whether this will optimize logging, it depends on how your queries may or may not use the index. You should also learn how to parse queries with EXPLAIN
.
You should also read more about storing passwords.
Here is a good blog on the topic: "You may be storing passwords incorrectly
source to share