有哪些方法可以改进这个长 mysql 搜索查询?

发布于 11-24 22:53 字数 703 浏览 0 评论 0原文

希望改进搜索查询的 mysql select 语句:

Select * from table WHERE ( user = '$search_query'
OR user LIKE '$search_query %'
OR keyword LIKE '$search_query'
OR tag LIKE '$search_query'
OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'
OR tag LIKE '$search_query%'
OR REPLACE(question, ',' ,'') LIKE '$search_query %'
OR REPLACE(question, ',' ,'') LIKE '% $search_query'
OR REPLACE(question, ',' ,'') LIKE '% $search_query %' 
OR REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(question, '\'', ''), ',', ''), '.',''), ':',''), ';',''), '!',''), '?','') LIKE '%$search_query%'
)

它之所以如此崩溃,是因为如果有人搜索“art”,我不希望它也显示“heart”的结果。

我确实需要拥有这些相同的功能,但运行更少的资源。

Looking to improve this mysql select statement for a search query:

Select * from table WHERE ( user = '$search_query'
OR user LIKE '$search_query %'
OR keyword LIKE '$search_query'
OR tag LIKE '$search_query'
OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'
OR tag LIKE '$search_query%'
OR REPLACE(question, ',' ,'') LIKE '$search_query %'
OR REPLACE(question, ',' ,'') LIKE '% $search_query'
OR REPLACE(question, ',' ,'') LIKE '% $search_query %' 
OR REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(question, '\'', ''), ',', ''), '.',''), ':',''), ';',''), '!',''), '?','') LIKE '%$search_query%'
)

The reason it's broke down the way it is, is because say if someone searches for "art", I don't want it showing results for "heart" as well.

I really need to have of these same functions but running fewer resources.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

你的呼吸2024-12-01 22:53:28

如果您的表是 MyISAM,您可以在其上创建 FULLTEXT 索引:

CREATE FULLTEXT INDEX fx_mytable_user_tag_question ON mytable (user, tag, question)

SELECT  *
FROM    mytable
WHERE   MATCH(user, tag, question) AGAINST ('+some*' IN BOOLEAN MODE)

此查询将返回 somesomething但不是很棒

实际上,第一步(创建索引)不是必需的,但是,它将加快查询速度并允许更复杂的搜索(不限于BOOLEAN MODE)和相关性范围。

默认情况下,搜索查询的最小长度为 4 个字符,因此将找不到示例中提到的 art

要解决此问题,您必须更改服务器设置中的参数 ft_min_word_len

If your table is MyISAM, you can create a FULLTEXT index on it:

CREATE FULLTEXT INDEX fx_mytable_user_tag_question ON mytable (user, tag, question)

SELECT  *
FROM    mytable
WHERE   MATCH(user, tag, question) AGAINST ('+some*' IN BOOLEAN MODE)

This query will return some and something but not awesome.

Actually, the first step (creating the index) is not required, however, it will speed up the queries and allow more complex searches (not limited to BOOLEAN MODE) and relevance ranging.

By default, minimal length of a search query is 4 characters, so art mentioned in your example would not be found.

To work around this, you will have to change parameter ft_min_word_len in server settings.

窝囊感情。2024-12-01 22:53:28

看起来

OR tag LIKE '$search_query'
OR tag LIKE '$search_query%'

只会返回相同的结果,仅运行

OR tag LIKE '$search_query%'

OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'

搜索此问题相同,我了解到 MySQL 支持 正则表达式搜索。使用这些,如果您能够指定 % 应匹配的模式。这将有助于解决所有那些讨厌的空格,因为您可以只使用 \s?

It looks like

OR tag LIKE '$search_query'
OR tag LIKE '$search_query%'

would return identical results to only running

OR tag LIKE '$search_query%'

Same with

OR tag LIKE '% $search_query'
OR tag LIKE '% $search_query%'

Searching around for this question, I learned that MySQL supports regular expression searches. Using these, if you are able to specify what pattern the % should match. This would help with all those pesky spaces, because you could just use \s?.

舞袖。长2024-12-01 22:53:28

重组数据库,使标签存储在单独的表中,问题上的每个标签对应一行,以便您可以运行如下查询:

SELECT * FROM question
WHERE question_id IN (
  SELECT question_id FROM question_tags WHERE tag = '$search_query'
) OR <...>

根据您希望搜索关键字的工作方式,您可以使用类似的查询解决方案在那里。

Restructure your database such that tags are stored in a separate table, with one row for each tag on a question, so that you can run a query like:

SELECT * FROM question
WHERE question_id IN (
  SELECT question_id FROM question_tags WHERE tag = '$search_query'
) OR <...>

Depending on how you want search keywords to work, you may be able to use a similar solution there.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文