Joining on Multiple SphinxSE Tables In Single Query

Question

I'm trying to write a query that joins a handful of tables from MySQL with two SphinxSE tables. It's basically an "each row has two names" type database, and those two names are each an index that is linked to a SphinxSE table... my goal is to do a search that is basically "either one of the names match this string in Sphinx".

The query I've tried using is:

SELECT * from names
LEFT JOIN name_1_se ON name_1_se.id=names.name_1_id
LEFT JOIN name_2_se ON name_2_se.id=names.name_2_id
WHERE name_1_se.query=('some random name;mode=phrase;limit=100000;maxmatches=100000')
OR name_2_se.query=('some random name;mode=phrase;limit=100000;maxmatches=100000')

(The name_1_se and name_2_se tables are SphinxSE tables).

If I use just a single lookup in the where, it works fine... adding the second clause (either one) that forces two SphinxSE 'lookups' forces the results to return empty.

Is what I am trying to do possible, or is it a known issue with SphinxSE? The closest I could find on the Sphinx site was this bug http://sphinxsearch.com/bugs/view.php?id=255 from 2008.

Thanks!

barryhunter barryhunter · Accepted Answer · 2011-12-02T13:36:01

No this wont work. It because of the 'artitecture' of sphinxSE/mysql.

Sphinx of course doesnt present real tables to mysql. It pretends its a table. It tells the mysql query optimiser works very well via indexes. So the optimizer should always pick the SphinxSE table 'first', which is then joined with the real mysql table.

So for each row, it does an index scan on the SphinxSE table, which gets the doc_ids. and it then looks them up in the origial table (the join itself).

SphinxSE cant exist on the 'right' side of a Join. Always must be first (or left).

... your query (using left join) is forcing mysql to put the sphinxSE table (well tables no less!) on the right, which simply wont work.

You need to reform the query as a single sphinxSE table. A sphinxSE table can search multiple sphinx indexes at once. Or can maybe reform your actual indexes to make once index.

Remember can be multiple fields per index - so an extended query could perhaps be used

(@field1 "some random name") | (@field2 "some random name")

... result one sphinxSE table (on the left of the join :) ).

Updated to add:

Creating a sphinxSE to search two indexes at once (like a sql UNION) pretty sure you can do

CREATE TABLE ... CONNECTION="sphinx://localhost:9312/index1,index2";

but even if you cant, overriding it at query time should definitely work, example from the docs:

... WHERE query='test;index=test1,test2,test3;';

The index defined in the table will be ignored, so can use any sphinxSE table.

Joining on Multiple SphinxSE Tables In Single Query

1 Answers