If it's an int, I know it will be faster, just cannot understand the string type.
notes: most Asian language don't have the space between words. and mysql cannot split the sentence to words. and also, I mean random search, that is, words can appears in any where in a sentence .
An index is sorted, a table is not. Therefore, when you're searching on an index, it's got a clue as to where to find the string, even if there's a different value for each row in the table.
Moreover, indices are smaller (generally) than the table, so to scan each column in the table, you have to go over each row. An index seek is just finding the right spot in the index, selecting that, grab the pointer to the clustered index, and away you go to get the rest of the row.
The index is essentially a 2-column table, with the indexed field in sorted order along with the PK lookup. SO for a string, it has the strings in sorted order. A search then can be done using a binary search instead of a table scan, which is going to be way faster for almost any length of table.
The index is essentially like an index in a book, where every word (depending on the book) that appears in the book is placed in the index, with the page numbers where that word appears. The index is alphabetically sorted, so it's quick to find the word in the index. If you didn't have the index in a book, the only way to find every instance of a specific word is to read through the entire book, noting where that word appears.
A table is just the same. If you search for a record that has a specific column value, and you don't have an index, the only thing the database can do is iterate through the entire table to find any match.
A phone book is indexed on last name. Can you imagine how slow looking up a number would be if it wasn't?
One big point is that an index won't help at all for certain kinds of searches. For example:
SELECT * FROM [MyTable] WHERE [MyVarcharColumn] LIKE '%' + @SearchText + '%'
No amount of normal indexing will help that query. It's forever doomed to be slow. That LIKE expression is just not sargable.
Why? You first need to understand how indexes work. They basically take the columns being indexed along with the primary key (record pointer) into a new table. They then sort that table on the indexed column rather than the key. When you do a lookup using the index, it can very quickly find the row(s) you want because this index is sorted to facilitate a more efficient search using algorithms like binary search and others.
Now look at that query again. By placing a wildcard in front of the search text, you've just told the database that you don't know for sure what your column starts with. No amount of sorting will help; you still need to go through the entire table to be sure you find every record that matches the expression. And that means any normal index on the column is worthless for this query.
If you want to search a text column for a search string anywhere in the column, you need to use something a little different: a full-text index.
Now for contrast look at this query:
SELECT * FROM [MyTable] WHERE [MyVarcharColumn] LIKE @SearchText + '%'
This will work perfectly fine with an normal index, because you know how you expect the column to start. It can still match up with the sorted values stored in the index, and so we can say that it is sargable.
The simplest answer is another couple of questions: