什么是KEY for MySQL的'_idx'后缀？

Question

Tried searching for this but couldn't find anything that helped me understand.

In CodeIgniter's Session table creation query for example, there's a line:

KEY `last_activity_idx` (`last_activity`)

that comes after defining the column 'last_activity'.

What's this for?

Answer 1

That means the column last_activity is indexed, and the name of the index is last_activity_idx. An index means that running conditional queries based on that field will be faster.

For example, if you run a query like:

SELECT * FROM `session_table` WHERE `last_activity` = 1000

If the column is not indexed, MySQL will have to search through every row to check the value of that column. If that column is indexed, MySQL is able to more quickly find rows which match that column.

https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html