Search: FULLTEXT or LIKE?
Probably every developer approached the moment of choosing between two solutions of the same task and of course the decisive factor of choice is the fastest way (in terms of the process). In the course of developing a search for my CMS, I also asked myself a question: what is better to do a database search using a full-text index or using the comparison operator LIKE, having a small amount of information.
In order to answer my question, I had 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 for which the search was performed contained 255 characters, randomly selected from one large text. The search was made in the same way by random words not 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 for in both fields. At all stages, the search was first performed using the MATCH (field_name) AGAINST ('search_text') construct and then using LIKE.
')
Note: I tested AMD 64 X2 4200, 2GB RAM, Apache, MySQL 5.0 on my home computer.
The number of requests at each stage is 100. The frequency is once per second.
The graph shows the average execution time of search queries in 3 stages.
Honestly, I expected a little different dependence of time on the complexity of the query 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 established addition and Russian), which creates an additional load.
Benefits of searching with the LIKE operator:
Disadvantages of LIKE:
Benefits of full-text search:
Disadvantages:
The experiment does not claim to be ideal, but, I believe, it quite clearly shows the differences in speed between the two types of search, with a small database size. In my opinion, both variants have the right to exist, however, in different situations.
UPD: Different situations mean cases with different numbers of rows in a 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, due to the fact that the search is performed on all records.
PS: read about full-text search here .
In order to answer my question, I had 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 for which the search was performed contained 255 characters, randomly selected from one large text. The search was made in the same way by random words not 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 for in both fields. At all stages, the search was first performed using the MATCH (field_name) AGAINST ('search_text') construct and then using LIKE.
')
Note: I tested AMD 64 X2 4200, 2GB RAM, Apache, MySQL 5.0 on my home computer.
The number of requests at each stage is 100. The frequency is once per second.
Test results
The graph shows the average execution time of search queries in 3 stages.
Honestly, I expected a little different dependence of time on the complexity of the query 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 established addition and Russian), which creates an additional load.
Advantages and disadvantages
Benefits of searching with the LIKE operator:
- a slight increase in request processing time as its complexity increases
- ability to sort the results
- versatility: can be used to search rpakticheski for any type of field, in contrast to the full text
Disadvantages of LIKE:
- lack of morphology support
- no modifiers
- search all lines
Benefits of full-text search:
- morphology support
- issue of results by relevance
- the presence of modifiers that are similar to the modifiers in Google and Yandex
- stop words
- ability to customize
Disadvantages:
- lack of sorting
- support only VARCHAR and TEXT fields with indexes FULLTEXT
- resource-intensive process
- initial support for MyISAM tables only
- with the FULLTEXT key set, adding data to the table takes longer
Epilogue
The experiment does not claim to be ideal, but, I believe, it quite clearly shows the differences in speed between the two types of search, with a small database size. In my opinion, both variants have the right to exist, however, in different situations.
UPD: Different situations mean cases with different numbers of rows in a 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, due to the fact that the search is performed on all records.
PS: read about full-text search here .
Source: https://habr.com/ru/post/25646/
All Articles