Convering index does not work with where caluse

SQL++
1

Update: It looks like order of index matters.

Index 1

create index idx_fqid_form on bucket(fqid,form) where form='attribute';

Index 2

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?

Question

Hello,

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?

2

Hi @moon0326,

The WHERE clause determines index usage. You can do the following to use either index:

EXPLAIN SELECT fqid, form
FROM bucket
WHERE form='attribute' AND fqid IS NOT MISSING;
1 Like
3

Ah that makes sense.

Thank you so much!