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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments