Please consider this:
create table tbl1 (col1 text primary key);
insert ...
select * from tbl1 where substr(col1,1,1)='a';
select * from tbl1 where col1='a';
According to explain (I'm on 3.8.6), the first select above causes a scan
of the entire table, while the second select uses the index. My thinking is
that for a specific function request such as substr(x,1,y) the index could
just as well be searched instead. I see benefits in a query like this:
Instead of coding:
select * from tbl1 where col1 like 'a_c%';
one could use:
select * from tbl1 where substr(col1,1,1) in ('A','a') and col1 like 'a_c%';
This COULD have a huge impact on the number of rows that need to be
searched.
Staffan
