混合 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屋！