Searchable content
Context: Searchable knowledge base A search for the word "crash" should also match "crashed", "crashes" and "crashing".Search with query
Antipattern: Using SQL for search Searching using SQL and making the solution both fast and accurate is difficult. One fundamental principle of SQL is that a value in a column is atomic. You can compare a value to another value. But you always compare the whole value when you do that. Comparing subsrings is inefficient or inaccurate in SQL. The disadvantage of pattern-matching is that they have poor performance.
SELECT * FROM bugs WHERE description LIKE %crash%;
SELECT * FROM bugs WHERE description REGEXP 'crash';
Search engine
Solution: Use the right tool for the job It's best to use a specialized search engine tehnology, instead of SQL. Another alternative is to reduce the recurring cost of search by saving the result. MySQL provides a simple full-text index type for the storage engine (MyISAM). You can define a full-text index over columns of type CHAR, VARCHAR, or TEXT.
ALTER TABLE bugs ADD <span class='keyword_code'>FULLTEXT INDEX</span> bugfts (summary, description);
Use the MATCH function to search for a key word among the indexed text.
SELECT * FROM bugs
WHERE <span class='keyword_code'>MATCH(summary, description)</span> AGAINST ('crash');
Custom Solution
If you don't want to install a search engine product, you can roll your own. Basicaly, an inverted index is a list of all words one might search for. For example, define a table Keywords to list the terms for which users will search. Also, define an intersection table BugsKeywords. If no one has searched for the given keyword before, we need to search the hard way.
Last update: 221 days ago