Firebird SQL index on multiple columns

This is for Firebird 2.5.

I have a table T with an index made of 2 columns, say ColA and ColB. If I'm doing : SELECT * FROM T WHERE ColA=..., so the WHERE clause is only on column A, will Firebird put a default value for column ColB, and benefit of the index, or it cannot use at all this index?

A bit of context: I'm doing a db upgrade. Here is what I have:

CREATE TABLE user(
     newid BIGINT NOT NULL,
     oldid BIGINT NOT NULL,
     anotherCol INT);

CREATE INDEX idx ON  user(oldid, anotherCol);
     
CREATE TABLE order(
     RefUser BIGINT);

order.RefUser were oldid and I need to change them to newid. I do it using this query:

UPDATE order o SET o.refuser = (SELECT u.newid FROM user u WHERE u.oldId = o.refuser);

At this point of time, oldid is still unique, but later on the uniqueness will only be guaranteed for (oldid, anotherCol), hence the index, and the creation of newid.

User table is a few million of records, order table is a few dozens of millions: this query takes more than an hour. I would like to see how to improve it (not keen on shutting down a critical service for that amount of time).

Solution

Assuming the index statistics are up-to-date, or at least good enough for the optimizer, then Firebird can (and often will) use a multi-column index when not all columns are part of the where-clause. The only restriction is that it can only use it for the first columns (or the 'prefix' of the index).

So with

CREATE INDEX idx ON  user(oldid, anotherCol);

Firebird can use the index idx just fine for where oldid = 'something', but not for where anotherCol = 'something'.

And no, Firebird does not "put a default value for column [anotherCol]". It does a range scan on the index and returns all rows that have the matching oldid prefix.

Technically, Firebird creates index keys by combining the columns as described in Firebird for the Database Expert: Episode 1 - Indexes, which means the value in the index is something like:

0<oldid> 1<anotherCol> : row_id

e.g. (simplified, as in real life Firebird also does a prefix compression)

0val1 1other1 : rowid1
0val1 1other2 : rowid4
0val1 1other3 : rowid6
0val2 1other1 : rowid2
...

When using where oldid = 'val1', Firebird will search the index for all entries that start with 0val1 1 (as if it was doing a string search for 0val1 1% on a single column). And in this case it will match rowid1, rowid4 and rowid6.

Although this works, if you query a lot on only oldid, it might be better to also create a single column index on oldid only, as this index will be smaller and therefor faster to traverse when searching for records. The downside of course is that more indices have a performance impact on inserts, updates and deletes.