CODEWITHSUNDEEP
mysqlsphinx
MacMac
MacMac

Reputation: 35301

Using Sphinx for the first time - configuring the sql_query key

I'm currently practicing using Sphinx, I've not far off done much, except the configuration what I'm trying to do. The sql_query key is leaving me somewhat confused what to put there, I read in the Sphinx documentation of sql_query but it doesn't seem to clear my mind from knowing what to do since I have many SELECTs in my web application, and I want to use Sphinx for my search and the SQL is often changed (upon user search filtering).

As of my search using MySQL, I want to integrate Sphinx to my web application, if the sql_key is not optional, do I have to expect to put the whole search SQL query into that field or do I pick out the necessary fields from tables to start a reindex?

Can someone point me to the right direction so I can get things going well with Sphinx and my web application.

Upvotes: 1

Views: 1254

Answers (2)

Shantha Kumara
Shantha Kumara

Reputation: 3421

You can create a sql query to get union set of records from the Database. If you do multiple table joining and query to select the best result set, you can do it with Sphinx too.

You may run into a few trouble with your existing table structure in the database.

Like :

  • Base table does not have integer primary key field

    Create a new table which has two fields. One for the integer id field and the other field to hold the primary key of the base table. Do an inner join with that table and select the id field from that table. Eg. SELECT t1.id, t2.name, t2.description, t2.content FROM table_new t1 INNER JOIN table_2 t2 WHERE t1.document_id = t1.thread_id INNER JOIN REST_OF_YOUR_SELECT_QUERY

    The ta.id is for Sphinx search engine to do its internal indexing.

  • You filter data by placing WHERE clause and filtering

    You can do that in Sphinx by setting filters dynamically based on the conditions.

  • You select and join different tables to get results

    This also can be done by setting different sources and indexes based on your requirements.

Hope this would help you to get an understanding what you need to add and modify to start thinking how Sphinx search engine can be configured to your requirements. Just come here again if your need more help.

Upvotes: 0

aditirex
aditirex

Reputation: 692

sql_query is mandatory , it's run by sphinx to get the data you want to be indexed from mysql . You can have joins , conditions etc. , must be a valid sql query . You should have something like "SELECT id ,field1,field2,fieldx from table" . id must be a primary id .Each row returned by this query is considered a document ( which is returned by sphinx when you search ) . If you have multiple tables ( that are very different by meaning - users , articles etc.) - you need to create an index for each . Read tutorials from here : http://sphinxsearch.com/info/articles/ to understand how sphinx works .

Upvotes: 2

Related Questions