I have a fairly simple table with three fields, all three of which are part of a compound NOTUNIQUE SBTREE index. For sake of example, let’s say these fields are x, y, and t, where x and y are STRINGs and t is a LONG.
This statement will instantly return a record (it appears to be the first matching record, but since I’m not supplying an ORDER BY clause, I believe I can’t depend on that):
SELECT FROM table WHERE x = 'a' AND y = 'b' LIMIT 1
Doing an EXPLAIN shows that it’s using the index and only reading one record.
But if I do this instead:
SELECT FROM table WHERE x = 'a' AND y = 'b' ORDER BY t LIMIT 1
The query takes forever and reads all 600,000+ records in the table (that match x and y).
My real objective is this (fetching the “last” record based on the value of t):
SELECT FROM table WHERE x = 'a' AND y = 'b' ORDER BY t DESC LIMIT 1
But that behaves the same way as the first ORDER BY query. Adding the DESC does indeed return the last record that matches x and y, but it does so by doing a table scan.
I’ve also tried selecting directly from the index and I’ve tried using MATCH, but neither of those approaches were successful, because there does not appear to be a way to control the ORDER, so as to get the last record instead of the first.
My ultimate objective is to quickly fetch the last record in this table, knowing x and y, but not knowing t. I’ve found several queries that produce that result, but all of them are essentially doing a table scan, which at several minutes in duration, I can’t afford.
Am I missing something here, or is there no way to quickly find the last record in a compound index?