CODEWITHSUNDEEP
mysqldatabase-designfull-text-searchsphinx
fixception
fixception

Reputation: 77

Sorting redundancies while fetching MySQL entries using ordered Sphinx Search output

I have a MySQL table that I indexed using Sphinx, with a bunch of columns as attributes that I want to let my users sort their search results by (e.g. name, ratings, etc.).

So I tell Sphinx to do this (for example, in PHP):

$sphinx = new SphinxClient();

// Retrieve $query, $sort_attr, and $order from $_GET    

$sphinx->SetMatchMode(SPH_MATCH_ANY);
$sphinx->SetArrayResult(true);

$sphinx->SetSortMode($order, $sort_attr);
$sphinx->SetLimits( /* something reasonable, <1000 */ );
$results_sphinx = $sphinx->Query($query, 'table');

This works and I get my ordered results.

I also want to display all the attributes (and some other columns that should remain unindexed) as part of the search results. This means that I have to fetch each item of the search results from the DB.

So I make the following MySQL call:

SELECT id, colA, colB, [...] FROM table WHERE table.id IN ([IDs returned from Sphinx, in some sorted order])

However, even if my list of IDs returned from Sphinx are in some sorted order according to the attribute columns (e.g. alphabetical order), WHERE IN will return results in the order of the table's index column, which in this case is the IDs themselves.

The only option I have in mind is to use ORDER BY:

SELECT id, colA, colB, [...] FROM table WHERE table.id IN ([IDs returned from Sphinx, in some sorted order]) ORDER BY [attribute] [DESC|ASC]

This works, but I just made both Sphinx and MySQL sort the same set of data for each search instance. This feels sub-optimal. I don't think I can leave the sorting to the latter MySQL call either, as I intend to have pagination in my results, so the IDs returned from Sphinx have to be in some order to begin with.

Can StackOverflow find me a way to avoid this redundancy? Please pick apart anything that I did above.

Thanks!

Upvotes: 0

Views: 356

Answers (1)

William King
William King

Reputation: 1268

How many IDs are you returning at a time? If it isn't many I would suggest using the MySQL ORDER BY FIELD as such

SELECT id, colA, colB, ... FROM table WHERE table.id IN (id1,id2,id3,...) ORDER BY FIELD (table.id,id1,id2,id3,....)

I do the exact same thing for my Sphinx/MySQL searches and retrievals, works great, never had a slow query (although I'm only fetching between 6 and 12 IDs at a time).

Upvotes: 2

Related Questions