Geek Answers Handbook

Sql Single Index or Multiple Index

Let's say I have a Products table.

In my user interface, I allow the user to search by name, description, code. The user can only search by criteria.

Should I create an index for each criterion: name, description, code, or create ONE single index for all 3?

What makes you choose one against the other?

+5
source share
5 answers

You need an index for everyone. One index for all three will be useful only for searches related to the first criterion in the index, or in combination with the second, etc.

+8
source

, , create index .. on T(A, B, C), , . A, . A B, . A B C, . B C B C.

, , . , , , . , , , , Full Text.

+9

: .

- , , , () . / ...

, , ( , ) .

+3

start-with ('search%'), . - , seeked , .

('% search%') , . , scanned .

,

SET STATISTICS IO ON
SET STATISTICS TIME ON
+1

, . :

  • A single index (per column) is sufficient no matter how you order your results (ASCending, DESCending)

  • For columns such as title and description, you can consider a full text search instead of the usual SQL comparisons and the LIKE filter.

+1
source

More articles:


All Articles