Description
Consider travel sample with the following index:
CREATE INDEX faa_index ON airport(faa: string);
If you run the following queries using regular comparators the FAA index is used:
SELECT * FROM airport WHERE faa = 'LAX';
SELECT * FROM airport WHERE faa >= 'LAX';
But if you run these closely related queries using LIKE the FAA index is NOT used:
SELECT * FROM airport WHERE faa LIKE 'LAX';
SELECT * FROM airport WHERE faa LIKE 'LAX%';
Virtually every other database on the planet would use the index - Analytics needs to as well, as customers coming from the RDBMS world will expect it and be surprised and annoyed to discover that we currently don't do this. (It surprised me while teaching not to long ago.)
We should fix this ASAP, certainly before Goldfish ships, if we want to make a good first impression.
For extra credit we should exploit the index even for "... LIKE '%LAX%" – it's still a significant query time-saver to scan the index leaves instead of the base collection. (As a general practice we should push auxiliary predicate processing into our scans.)
Attachments
Issue Links
- depends on
-
MB-56789 Utilize secondary indexes with "arbitrary" expressions
- Open