将索引添加到MySQL表

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.

MySQL文档对此不太清楚。我想为现有表添加索引。该表是一个带有登录ID和密码的用户表,我想为此创建一个索引来优化登录。

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'.

这实现了我想做的事吗? (优化登录?)以前我只在一个名为'id'的字段上有一个主键。

2 个解决方案

#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.

是的,这实现了创建索引。索引名为“name”(如果您没有为索引指定名称,它会尝试使用您在索引中指定的第一列)。索引是两列的组合:位置1中的名称和位置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.

至于这是否会优化日志记录,这取决于您的查询可能会或可能不会使用索引。您还应该了解如何使用EXPLAIN分析查询。

You should also read more about storing passwords.

您还应该阅读有关存储密码的更多信息。

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

这是一个关于这个主题的好博客:“你可能错误地存储了密码”

#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.

除非用户名在您的应用程序中不是唯一的,否则我认为索引“密码”并不合理。索引将更大,插入将更慢,没有附加值。

#1