What is the best approach to index first name and last name of a person? Lets assume that users can search first name, last name and both of them together. Should i have to create multiple column index or seperate indexes? Should i have to use index prefixes? Also we can assume that people are from different nations. Would using prefixes be unnecessary in that case?
索引一个人的名字和姓氏的最佳方法是什么?让我们假设用户可以一起搜索名字,姓氏和两者。我应该创建多个列索引还是单独的索引?我应该使用索引前缀吗?我们也可以假设人们来自不同的国家。在这种情况下使用前缀是不必要的吗?
3 个解决方案
#1
4
Situation is just clear.
情况很清楚。
Create index on surname, and optionally add first name to the same index on second position.
在姓氏上创建索引,并可选择将名字添加到第二个位置的同一索引。
This is the result of frequent used business rule, that searching is done by surname. Surname is the most selective, and any searching form bases on surname. (of course if you are not designing application for Icelandic company).
这是经常使用的业务规则的结果,搜索是通过姓氏完成的。姓氏是最具选择性的,任何搜索形式都基于姓氏。 (当然,如果您没有为冰岛公司设计申请)。
Adding first name is optional, and depends on size of table
添加名字是可选的,取决于表的大小
#2
3
-
You should create a First Name/Last Name multi-column index.
您应该创建名字/姓氏多列索引。
-
You should create a Last Name/First Name multi-column index.
您应该创建一个姓氏/名字多列索引。
Note that inserts and update will be slower (you don't get something for nothing).
请注意,插入和更新将更慢(您没有得到任何东西)。
#3
1
I'm not sure I'm understanding your question correctly, but your best bet would be to create a nonclustered index on the most queried fields. For instance, you can create a nonclustered index on firstname and lastname. You should see performance gains through that.
我不确定我是否正确理解了您的问题,但最好的办法是在查询最多的字段上创建非聚集索引。例如,您可以在firstname和lastname上创建非聚簇索引。你应该看到通过它获得性能提升。
#1
4
Situation is just clear.
情况很清楚。
Create index on surname, and optionally add first name to the same index on second position.
在姓氏上创建索引,并可选择将名字添加到第二个位置的同一索引。
This is the result of frequent used business rule, that searching is done by surname. Surname is the most selective, and any searching form bases on surname. (of course if you are not designing application for Icelandic company).
这是经常使用的业务规则的结果,搜索是通过姓氏完成的。姓氏是最具选择性的,任何搜索形式都基于姓氏。 (当然,如果您没有为冰岛公司设计申请)。
Adding first name is optional, and depends on size of table
添加名字是可选的,取决于表的大小
#2
3
-
You should create a First Name/Last Name multi-column index.
您应该创建名字/姓氏多列索引。
-
You should create a Last Name/First Name multi-column index.
您应该创建一个姓氏/名字多列索引。
Note that inserts and update will be slower (you don't get something for nothing).
请注意,插入和更新将更慢(您没有得到任何东西)。
#3
1
I'm not sure I'm understanding your question correctly, but your best bet would be to create a nonclustered index on the most queried fields. For instance, you can create a nonclustered index on firstname and lastname. You should see performance gains through that.
我不确定我是否正确理解了您的问题,但最好的办法是在查询最多的字段上创建非聚集索引。例如,您可以在firstname和lastname上创建非聚簇索引。你应该看到通过它获得性能提升。