Search code examples
mysqlsearch-enginesphinxperformance-testing

Why is this sphinx query so slow? ORDER BY id DESC

SELECT id FROM postalcodes ORDER BY id DESC LIMIT $x, 1

$x is a number < 1000 (due to max_matches) and postalcodes index contains ~1,1 million rows.

Query time: 0,17 seconds

Why does it take so long time? Is there a better way to query in the end of the index?

Solution

  • Why? The only way for sphinx to execute that query as is, is to get all the rows, sort them, then iterate though to find the row you want.

    As noted in the comments, sphinx doesnt really have the concept of indexes like mysql does. (in theory sphinx should be used for full-text search, which uses the special inverted index)

    Some discussions here, http://sphinxsearch.com/forum/view.html?id=5683 on possible workarounds.

    Using reverse_scan, and possibly cutoff, might well help you. 

    SELECT id FROM postalcodes LIMIT $x, 1 OPTION reverse_scan = 1, cutoff = 1000;

    Although if you end up adding a 'MATCH' to the where, then you end up not honouring implicit sort, so the query wont work without the ORDER BY, in which case reverse_scan wont help.