Are you prepared to go mad? If so, compare these to statments and their results:
mysql> SELECT warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc' ORDER BY warehouse.date DESC,warehouse.NUM DESC LIMIT 50; Empty set (0.00 sec) mysql> SELECT warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc'; +---------+------------+ | NUM | date | +---------+------------+ | AT00105 | 2015-06-17 | +---------+------------+ 1 row in set (0.00 sec)
What's going on is that MySQL is asking searchd (part of Sphinx) to do a full text search on 2 indexes. It then does a join on the results. With the ordering, I get zero results. Without ordering I get the expected results.
This shouldn't be happening. This can't be happening.
But then I found the answer: The first query in the example above was a cut and paste from the query log on my dev VM. This means that MySQL had already run that query and (more importantly) cached the results. The Sphinx indexes had been updated in the meantime. But searchd can't tell MySQL to invalidate the query cache.
mysql> SELECT SQL_NO_CACHE warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc' ORDER BY warehouse.date DESC,warehouse.NUM DESC LIMIT 50; +---------+------------+ | NUM | date | +---------+------------+ | AT00105 | 2015-06-17 | +---------+------------+ 1 row in set (0.00 sec)
Sanitiy is restored.
Long and short of this is to ALWAYS use SQL_NO_CACHE when using the Sphinx plugin.
