join mysql and sphinx

Question

I have a very small MySQL database that stores information about goods and users. I am trying to implement search among users, who bought some goods by firstname and lastname. Sphinx search engine has lot of good recommendations. So I am using it. Now my search looks like following:

1. Search with Sphinx IDs of users according to firstname and lastname.

2. Search in MySQL (not with Sphinx) goods according to specific filters (id or category, price, etc.) where user_id IN IDs from item1.

How to implement this with one JOIN query?

Answer 1

You can't directly, because as you say the sphinx index, and the database live within different 'systems'.

So the 'join' is happening in your application. Sounds like you are already implemening what is effectivly a join.

But there are two alternatives if you really dont want to continue with that system,

1) SphinxSE. Its a fake mysql storage engine, when you make a query against the virtual table, a query is made in the background back to sphinx index, and the results of the query are presented as a table, to mysql. Now because its a mysql table, mysql then join it with the database table(s) to present resultset, combinging the query and the data. (there is still seperate systems, but mysql implements the joining logic)

2) Attributes. Can store data in the sphinx index, alongside the full-text index. Sphinx can return the attributes in result sets. In this way you avoid the need for the join, because you get the search results along with the data (which you would of got from mysql) in one go. (in this way you create one big 'normalized' index)