May 18, 2008 at 16:09Search: FULLTEXT or LIKE?
Probably every developer came to the moment of choosing between two solutions of the same problem and of course the decisive deciding factor is the fastest way (by process time). So while developing a search for my CMS I asked myself: what is better, do a database search using a full-text index or using the LIKE comparison operator, having a small amount of information.
In order to answer my question, I spent a little experience: I created a table with four fields (two of which were used for searching and were indexed by FULLTEXT) containing 5,000 rows. The fields used to search contained 255 characters, randomly selected from one large text. The search was also performed on random words no shorter than 4 characters.
At the first stage, one word was searched in one field. At the second stage, one of two words was searched in one field. At the third stage, one of two words was searched in both fields. At all stages, the search was performed first using the MATCH (field_name) AGAINST ('searched_text') construct, then using LIKE.
Note: tested on a home computer AMD 64 X2 4200, 2GB RAM, Apache, MySQL 5.0.
The number of requests at each stage is 100. Frequency - once per second.
The graph shows the average execution time of search queries in 3 stages.
Honestly, I expected a slightly different time dependence on the complexity of the request and a clear boundary between the types of search queries. But this is explained by the fact that the search by the FULLTEXT index takes into account the morphology of the word (with the addition installed and Russian), which creates an additional load.
Benefits of searching with the LIKE operator:
LIKE disadvantages:
Benefits of full-text search:
Disadvantages:
The experiment does not claim to be ideal, but, I think, it quite clearly shows the differences in speed between the two types of search, with a small database size. In my opinion, both options have a right to exist, however, in different situations.
UPD: Different situations mean cases with a different number of rows in the table. The LIKE operator is better when there are not a huge number of records and it is not applicable in tables with hundreds of thousands of rows, because the search is performed on all records.
PS: read about full-text search here .
In order to answer my question, I spent a little experience: I created a table with four fields (two of which were used for searching and were indexed by FULLTEXT) containing 5,000 rows. The fields used to search contained 255 characters, randomly selected from one large text. The search was also performed on random words no shorter than 4 characters.
At the first stage, one word was searched in one field. At the second stage, one of two words was searched in one field. At the third stage, one of two words was searched in both fields. At all stages, the search was performed first using the MATCH (field_name) AGAINST ('searched_text') construct, then using LIKE.
Note: tested on a home computer AMD 64 X2 4200, 2GB RAM, Apache, MySQL 5.0.
The number of requests at each stage is 100. Frequency - once per second.
Test results
The graph shows the average execution time of search queries in 3 stages.
Honestly, I expected a slightly different time dependence on the complexity of the request and a clear boundary between the types of search queries. But this is explained by the fact that the search by the FULLTEXT index takes into account the morphology of the word (with the addition installed and Russian), which creates an additional load.
Advantages and disadvantages
Benefits of searching with the LIKE operator:
- a slight increase in request processing time while increasing its complexity
- ability to sort results
- versatility: can be used to search virtually any type of field, unlike full-text
LIKE disadvantages:
- lack of morphology support
- lack of modifiers
- search all rows
Benefits of full-text search:
- morphology support
- releasing relevance results
- the presence of modifiers similar to modifiers in Google and Yandex search
- stop words
- customization option
Disadvantages:
- lack of sorting
- support only VARCHAR and TEXT fields with FULLTEXT indices
- resource intensive process
- initial support for only MyISAM tables
- when the FULLTEXT key is set, data is added to the table longer
Epilogue
The experiment does not claim to be ideal, but, I think, it quite clearly shows the differences in speed between the two types of search, with a small database size. In my opinion, both options have a right to exist, however, in different situations.
UPD: Different situations mean cases with a different number of rows in the table. The LIKE operator is better when there are not a huge number of records and it is not applicable in tables with hundreds of thousands of rows, because the search is performed on all records.
PS: read about full-text search here .