混合 MySQL 和 Sphinx 查询 [英] Mixed MySQL and Sphinx queries
问题描述
我安装了 MySQL 和 Sphinx,并且在 LNMP 服务器上正常工作.现在我想将 Sphinx 子查询集成到现有的 MySQL 查询中.
I have MySQL and Sphinx installed and working properly on a LNMP server. Now I'd like to integrate a Sphinx sub-query into an existing MySQL query.
示例:
SELECT * FROM mysql_table
JOIN (SELECT id FROM sphinx_index MATCH ('keyword')) AS match_table
ON match_table.id = mysql_table.id
这可能吗?如果没有,我是否应该单独执行 Sphinx,然后在 MySQL 查询中使用 WHERE IN
,或者这会扼杀我从 Sphinx 获得的额外效率?
Is this possible? If not, should I do the Sphinx separately and then use WHERE IN
in the MySQL query, or will this kill the extra efficiency I'm getting from Sphinx?
推荐答案
使用 SphinxSEhttp://sphinxsearch.com/docs/current.html#sphinxse-overview
Use SphinxSE http://sphinxsearch.com/docs/current.html#sphinxse-overview
然后是
SELECT * FROM mysql_table
JOIN (SELECT id FROM sphinx_index WHERE query='keyword') AS match_table
ON match_table.id = mysql_table.id
虽然
SELECT * FROM sphinx_index INNER JOIN mysql_table USING (id) WHERE query='keyword'
更短更简洁.和更好地保持结果的顺序.
is shorter and more concise. And better maintains the order of results.
其中 'sphinx_index' 是一个 SphinxSE 表,它指向底层的 sphinx 索引.
Where 'sphinx_index' is a SphinxSE table, which points to underlying sphinx index.
这篇关于混合 MySQL 和 Sphinx 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!