5

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

3
  • Indexes cannot be used with LIKE '%text%' predicates. They can, however with LIKE 'text%'. With fulltext indexing, the situation may be different but I guess it would require more work than just creating an index.
    – BuahahaXD
    Commented Mar 19, 2015 at 7:21
  • If you are always checking all 4 columns, you may as well make a single FULLTEXT index on the 4 columns. (Or you could make 5 indexes.)
    – Rick James
    Commented Mar 19, 2015 at 15:47
  • @rickjames, my query may include multiple like conditions on same column, for eg : where column1 like %abc% and column1 like %xyz% and other columns etc. In this case making single fulltext index on 4 columns won't help i guess.
    – Naman Gala
    Commented Mar 19, 2015 at 16:19

4 Answers 4

7

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.

13
  • Thanks for your answer. Can you please clarify a bit on fulltext searches require some maintenance/housekeeping by the admin to keep up good performance. What maintenance/housekeeping?
    – Naman Gala
    Commented Mar 19, 2015 at 9:11
  • 1
    They are usually generated from the indexed texts (your columns in the FT index) and updated when there's DML on the table (DELETE/UPDATE/INSERT).
    – Falcon
    Commented Mar 19, 2015 at 9:34
  • 1
    DML Operations can trigger updates on indexes (e.g. text changes, row deleted, new row inserted). These need to be written to the index and it's gotta be fast - you cannot wait on the index sometimes. Then the database decides to not cleanly insert the new value but rather put it somewhere else so it can be found. Or it deletes it but does not consolidate the space afterwards - and then you get index fragmentation.
    – Falcon
    Commented Mar 19, 2015 at 9:44
  • 1
    The index is for the whole table, so for all records in the table. DML will trigger changes to all indexes that are affected (e.g. multiple indexes containing the same columns).
    – Falcon
    Commented Mar 19, 2015 at 11:06
  • 1
    You need to look at the index as a whole, for the whole table. It is some sort of file. If there's DML on the table the file slowly becomes fragmented.
    – Falcon
    Commented Mar 19, 2015 at 11:22
1

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();
0

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

3
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.