patternsqlMinor
How does like query work with indexed table
Viewed 0 times
withquerylikeindexedworkdoeshowtable
Problem
I am working in Spring-java and mysql.
I have to query the table of size
And In my sql select query I have to make like queries with %text% search on say 4 columns. Those 4 columns are varchar(200), having average text size of 30 character.
I have gone through few blogs and SO answers to understand about index and after reading I came to this question.
Will making those 4 columns individually
Thank you
I have to query the table of size
100k records. Table has say 10 columns.And In my sql select query I have to make like queries with %text% search on say 4 columns. Those 4 columns are varchar(200), having average text size of 30 character.
I have gone through few blogs and SO answers to understand about index and after reading I came to this question.
Will making those 4 columns individually
FULLTEXT index, makes difference in execution time of like query?Thank you
Solution
Let me help you to understand how a "standard" index works.
Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The database uses B-Trees instead of loading and sorting the table as the indexes don't require as much memory and less disk reads are required.
Now imagine that you try to binary search for a value, but you don't know the exact beginning or ending of the value. A binary search is basically not possible and you have to traverse nearly the whole tree to find every possibility.
Of course, there're cleverer techniques than this. MySQL is not that dumb, it uses a Boyer-Moore algorithm for this problem, but that doesn't mean you don't suffer a performance impact.
A Fulltext search index will of course help. It uses entirely different datastructures (Tries, Suffix-Trees). Reading the manual of MySQL, I also get the impression that fulltext searching is quite easy with it.
However, on most systems fulltext searches require some maintenance/housekeeping by the admin to keep up good performance. Often times fulltext indexes keep "mapping/index" tables for the tokens of the indexed texts. These tend to fragment which can impact query response time, as the index grows unecessarily larger. So from time to time, they should be defragmented and optimized. You might want to look into that.
Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The database uses B-Trees instead of loading and sorting the table as the indexes don't require as much memory and less disk reads are required.
Now imagine that you try to binary search for a value, but you don't know the exact beginning or ending of the value. A binary search is basically not possible and you have to traverse nearly the whole tree to find every possibility.
Of course, there're cleverer techniques than this. MySQL is not that dumb, it uses a Boyer-Moore algorithm for this problem, but that doesn't mean you don't suffer a performance impact.
A Fulltext search index will of course help. It uses entirely different datastructures (Tries, Suffix-Trees). Reading the manual of MySQL, I also get the impression that fulltext searching is quite easy with it.
However, on most systems fulltext searches require some maintenance/housekeeping by the admin to keep up good performance. Often times fulltext indexes keep "mapping/index" tables for the tokens of the indexed texts. These tend to fragment which can impact query response time, as the index grows unecessarily larger. So from time to time, they should be defragmented and optimized. You might want to look into that.
Context
StackExchange Database Administrators Q#95658, answer score: 7
Revisions (0)
No revisions yet.