Scalable full-text search based on user order
What are the options for creating scalable full-text search results that need to be sorted for each user? This is for PHP / MySQL (Symfony / Doctrine if needed).
In our case, we have a database of workouts that were performed by users. Workouts that the user has done before should be displayed at the top of the results. The more often they performed a workout, the higher it should appear in search matches. If that helps, you can assume that we know how many times the user has completed the workout beforehand.
Possible solutions
Sphinx . Use Sphinx to implement full text search, do all queries and sorts in MySQL. This seems promising (and there's a Symfony plugin out there!), But I don't know much about it.
Lucene . Use Lucene to do full-text searches and complete user tweaks in a query. As suggested in this thread . Also, use Lucene to get the results and then reorder them in PHP. However, both solutions seem clumsy and potentially invisible as the user can complete hundreds of workouts.
Mysql - there is no built-in full text support (InnoDB), so we will use LIKE or REGEX which does not scale.
a source to share
MySQL
has built-in support FULLTEXT
, but only on tables MyISAM
.
It Sphinx
is the fastest engine for most real tasks . However, it is an external index, so it can only update in a timely manner using a cron script.
Using SphinxSE
(pluggable interface MySQL
to Sphinx
) you can join tables MySQL
and Sphinx
in one query. However, the update will require an external script.
Since the number of workouts performed seems to be changing frequently, keeping it in Sphinx
will take too much effort to restore the index.
With, SphinxSE
you can write a query like this:
SELECT *
FROM workouts w
JOIN user_workouts uw
ON uw.workout = w.id
WHERE w.query = 'query query query;filter=user_id,$user_id'
AND uw.user = $user_id
ORDER BY
uw.times_performed DESC
a source to share
I'm not sure why you intend to use Lucene, it won't be possible. Hundreds of workouts for each user not much data.
Try using Solr / Lucene to search. It has a JSON / XML interface that will go well with your PHP interface. Store user-completed workout # in a database table. When the query is issued, grab the results from Solr and you can select from the database table and resort to PHP code. Should be fast enough and scalable. With Solr, maintaining an index is very easy; just issue an add / update / remove request to your Solr server.
a source to share