Mixed MySQL and Sphinx queries

Question

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.

Example:

SELECT * FROM mysql_table
JOIN (SELECT id FROM sphinx_index MATCH ('keyword')) AS match_table
ON match_table.id = mysql_table.id

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?

Answer 1

It seems I found the answer on another SO question:

Integrating Sphinx to MySQL

From reading this, it looks like Sphinx and MySQL are not as integrated as I had hoped. They need to be used on separate connections, so you can't combine queries. Oh well...

Answer 2

Use SphinxSE http://sphinxsearch.com/docs/current.html#sphinxse-overview

Then would be

SELECT * FROM mysql_table
JOIN (SELECT id FROM sphinx_index WHERE query='keyword') AS match_table
ON match_table.id = mysql_table.id

Although

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.

Where 'sphinx_index' is a SphinxSE table, which points to underlying sphinx index.