Switch to: V9V8V7V6V5

3. Multicolumn Indexes

An index can be defined on more than one column of a table. For example, if you have a table of this form:

  major int,
  minor int,
  name varchar

and you frequently issue queries like:

SELECT name FROM test2 WHERE major = constant AND minor = constant;

then it is possible to define index on the columns major and minor together.

CREATE INDEX ind_mm ON T1(major, minor);

Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.

We have added multicolumn indexes into Valentina DB mainly for compatibility with other DBs, to simplify the first step of transformation of existing databases, which have PK and FK based on 2-3 columns.

But we consider this as a bad database design. Valentina DB have tools that are more effective by the storage size and by speed, such as ObjectPtr and BinaryLink.

Imagining that you need to repeat/copy {f1,f2} Primary Key fields, in one or even few tables that contain a Foreign Key pointing to this PK. Even worse! Indexes of PK and FK fields will have these {f1,f2} values.