Re: Redundant indexes with multi-column foreign key indexes

HSQLDB can use a multi-column index when any sequence of columns
beginning with the first column is queried. Therefore SELECT * FROM C
WHERE a_id = ? AND b_id = ? will use the c_a_b_idx index.
 

The system index on the foreign key column is used when you delete
rows from table A to check if there are any rows referencing the
deleting rows.
 

It is true that with the c_a_b_idx the system index on a_id is
redundant. But the management of indexes is a complex issue and more
complexity would be added if the system index were to be removed
automatically when you add a multi-column index that can do the same
job. You cannot eliminate a system index unless you drop the constraint
that is backed by that index.
 

Fred Toussi

 

 

On Tue, Jan 10, 2017, at 16:10, Keith Squier wrote:

> I understand that HSQL can use a multi-column index when only the
> first column of the index is used in a query. I also understand that
> HSQL automatically creates indexes on foreign keys. This can lead to
> redundant indexes when creating a multi-column index that uses
> multiple foreign key columns. For example, I have the following setup.
>  

> CREATE TABLE A (

>    id UUID NOT NULL,

>    name VARCHAR(45) NOT NULL,

>    PRIMARY KEY (id));

>  

> CREATE TABLE B (

>    id UUID NOT NULL,

>    name VARCHAR(45) NOT NULL,

>    PRIMARY KEY (id));

>  

> CREATE TABLE C (

>    id UUID NOT NULL,

>    datetime TIMESTAMP NOT NULL,

>    a_id UUID NOT NULL,

>    b_id UUID NOT NULL,

>    PRIMARY KEY (id),

>    CONSTRAINT fk_c_a FOREIGN KEY (a_id) REFERENCES A (id),

>    CONSTRAINT fk_c_b FOREIGN KEY (b_id) REFERENCES B (id));

>  

> This creates an index for each of the foreign keys on C (columns a_id
> and b_id). I know for a fact that I query table C using a_id and b_id
> together frequently, so I would like to create an index on both
> columns.
>  

> CREATE INDEX c_a_b_idx ON C(a_id, b_id);

>  

> But HSQL can also use this new index (c_a_b_idx) when querying only on
> a_id. This means the automatically created index (SYS_IDX_blah) for
> a_id is redundant, useless, and only taking up space. When I try to
> drop the SYS_IDX_blah index, I get errors about lacking privilege or
> the object is not found.
>  

> How can I eliminate the redundant index? How can I remove the
> automatically created index or prevent it from being created?
> ----------------------------------------------------------------------
> --------
> Developer Access Program for Intel Xeon Phi Processors

> Access to Intel Xeon Phi processor-based developer platforms.

> With one year of Intel Parallel Studio XE.

> Training and support from Colfax.

> Order your platform today. http://sdm.link/xeonphi

> _________________________________________________

> Hsqldb-user mailing list

> [email protected]

> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user