Scalable Full Text Search With Per User Result Ordering

Posted by jeremy on Stack Overflow See other posts from Stack Overflow or by jeremy
Published on 2010-05-06T14:21:39Z Indexed on 2010/05/06 14:38 UTC
Read the original article Hit count: 230

Filed under:
|
|
|
|

What options exist for creating a scalable, full text search with results that need to be sorted on a per user basis? This is for PHP/MySQL (Symfony/Doctrine as well, if relevant).

In our case, we have a database of workouts that have been performed by users. The workouts that the user has done before should appear at the top of the results. The more frequently they've done the workout, the higher it should appear in search matches. If it helps, you can assume we know the number of times a user has done a workout in advance.

Possible Solutions

Sphinx - Use Sphinx to implement full text search, do all the querying and sorting in MySQL. This seems promising (and there's a Symfony Plugin!) but I don't know much about it.

Lucene - Use Lucene to perform full text search and put the users' completions into the query. As is suggested in this Stack Overflow thread. Alternatively, use Lucene to retrieve the results, then reorder them in PHP. However, both solutions seem clunky and potentially unscalable as a user may have completed hundreds of workouts.

Mysql - No native full text support (InnoDB), so we'd have use LIKE or REGEX, which isn't scalable.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about search