Say I have a query like the following:
假设我有如下的查询:
SELECT * FROM users WHERE username = 'test' AND somethingelse = 'test'
I'm wondering if it's necessary to index both columns for optimization. Does MySQL first find all username columns with the value 'test', and then search those results for somethingelse columns with 'test'? Or does it happen simultaneously?
我想知道是否有必要索引这两列以进行优化。MySQL是否首先查找所有带有值“test”的用户名列，然后搜索那些带有“test”的其他列?还是同时发生?
1 个解决方案
#1
4
Yes, you should. Every column and combination of columns that appears in a WHERE clause should have an index for efficient searching.
是的,你应该。WHERE子句中出现的每个列和列的组合都应该有一个索引，以便进行有效搜索。
You get away with it for primary key fields because an index is created for those by virtue of being declared a primary key. Other columns require that you index them.
对于主键字段可以使用它，因为通过将索引声明为主键来创建索引。其他列要求您对它们进行索引。
So you'll have an index for username and somethingelse columns.
用户名和其他列都有索引。
If username has to be unique, it might have its own index.
如果用户名必须是唯一的，它可能有自己的索引。
#1
4
Yes, you should. Every column and combination of columns that appears in a WHERE clause should have an index for efficient searching.
是的,你应该。WHERE子句中出现的每个列和列的组合都应该有一个索引，以便进行有效搜索。
You get away with it for primary key fields because an index is created for those by virtue of being declared a primary key. Other columns require that you index them.
对于主键字段可以使用它，因为通过将索引声明为主键来创建索引。其他列要求您对它们进行索引。
So you'll have an index for username and somethingelse columns.
用户名和其他列都有索引。
If username has to be unique, it might have its own index.
如果用户名必须是唯一的，它可能有自己的索引。