英文:
REGEXP alternative in MySQL for better performance
问题
在一个表格中，我们有一个列 'title'，并且我们正在使用正则表达式(REGEXP)来查找与第一个标题的任何单词匹配的其他相似标题，然而，当使用正则表达式来实现这一要求时，我们遇到了一些性能问题。
请问是否有人可以提出任何其他替代方法来实现这一要求？
示例：
title 1 = 'Human Resources Director'
我们尝试找到同一表中符合以下条件的其他标题。因此，我们需要包含(Human或Resources或Director)中任何一个单词的其他标题。
SELECT id, title FROM table_name WHERE title REGEXP '\\bHuman\\b|\\bResources\\b|\\bDirector\\b'
尽管我们在标题上有一个单独的索引，但它未被利用。
英文:
In a table, we have a column 'title' and we are using REGEXP to find any other similar title which matches any word of the first title, however, we are facing some performance issues while using the REGEXP to accomplish this requirement.
Can someone please suggest any other alternative to achieve the requirement?
Example:
title 1 = 'Human Resources Director'
we are trying to find other titles in the same tables with the below condition. So we need any other title that contains any one of the words(Human or Resources or Director).
SELECT id,title FROM table_name WHERE title REGEXP '\\bHuman\\b|\\bResources\\b|\\bDirector\\b'
Even though we have a separate index on the title it's not getting utilized.
答案1
得分: 3
你可以使用FULL Text Search在MySQL中使用全文搜索，你需要在要搜索的列上创建一个FULLTEXT索引。一旦你创建了索引，就可以使用MATCH()函数执行搜索。BOOLEAN MODE用于指定我们要执行布尔搜索，这允许我们使用运算符“+”和“-”指定搜索中必须包含或排除哪些词。
SELECT title FROM mytable WHERE MATCH(title) AGAINST('Human Resources Director' IN BOOLEAN MODE);
英文:
You can Use FULL Text Search To use Full-Text Search in MySQL, you need to create a FULLTEXT index on the column that you want to search. Once you have created the index, you can use the MATCH() function to perform the search. The BOOLEAN MODE is used to specify that we want to perform a boolean search, which allows us to use the operators "+" and "-" to specify which words are required or excluded from the search.
SELECT title FROM mytable WHERE MATCH(title) AGAINST('Human Resources Director' IN BOOLEAN MODE);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论