Geek Answers Handbook

For a char / varchar / text column, why does the index for this column speed up the search?

If it is an int, I know that it will be faster, just can not understand the type of string.

Notes: Most Asian languages ​​do not have a space between words. and mysql cannot split the sentence into words. and also, I mean random search, that is, words can appear anywhere in the sentence.

+5
source share
6 answers

One big point is that the index will not help at all for certain types of searches. For example:

SELECT * FROM [MyTable] WHERE [MyVarcharColumn] LIKE '%' + @SearchText + '%'

. . LIKE sargable.

? , . , ( ) . , . , , , .

. , , , . ; , , , . , .

, - : .

:

SELECT * FROM [MyTable] WHERE [MyVarcharColumn] LIKE @SearchText + '%'

, , , . , , , .

+6

, - . , , , , .

, (), , . - , , , .

+4

- :

  • / ?
  • / ?
+2

, ( ), , , . , . , - , , .

. , , , , - , .

+1

. , , ?

+1

An index is essentially a two-column table with an indexed field in sorted order along with a PK search. SO for a row, it has rows in sorted order. Then the search can be performed using binary search instead of scanning the table, which will be faster for almost any table length.

0
source

More articles:


All Articles