Update: It looks like order of index matters.
create index idx_fqid_form on bucket(fqid,form) where form='attribute';
create index idx_form_fqid on bucket(form,fqid) where form='attribute';
The first index does not work even with “USE INDEX” clause.
The second index uses covering indexes with “USE INDEX” clause.
What’s the reason behind it?
I have the following index definition.
create index idx_fqid_form on bucket(fqid,form) where form='attribute'
I expected to get covering indexes with the following query, but it does not use covering indexes.
explain select fqid, form from bucket where form='attribute'
Is there a way to get around it?