How does like query work with indexed table

Question

I am working in Spring-java and mysql.

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

Answer 1

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.

Answer 2

If You wish to execute a query like %a%, you can

Repository(interface) Code

@Query("SELECT p from Project p where p.skillSetRequired LIKE :skill% order by p.projectDeadline")
public Optional<List<Project>> findProjectsBySkill(@Param("skill") String skill);

And you can pass skill as skill = '%'+skill from your controller while calling findprojectsBySkill Method

eg Code in Controller would be like

@Autowired
private Repository repository;

List<Project>=repository.findprojectsBySkill('%'+skill).get();

Answer 3

First of all @Falcon explained correctly that the Index can't work with like if there is a % at the beginning.

But with the normal FULLTEXT IN NATURAL MODE it's only possible to search full words and the words have to be at least 4 chars long (if you have access to the database you can change this)

You might want to have a look at BOOLEAN MODE option in MySql Fulltext cause there you can use sth. like "abc test" or +abc -test. In the first example MySql would show exact matches and using the second one MySql would show rows which match abc but not test.

If you want to search a word where you only know some chars you can use * in Boolean Mode but it's quite slow.

Further reading information: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Answer 4

In addition to above answers, B-tree, cluster and fulltext indexes can be multiple and will work from left-to-right. For example in:

where `1` like '%1%' and `2` like '%2%' and `3` like '%3%'

You should have multiple fulltext index on (1,2,3) to achieve good performance.

If you have only one column in where condition, fulltext index on that column will greatly increase performance.

By the way, both MyISAM and InnoDB engines support fulltext indexes for MySQL >= 5.6