Redundant indexes with multi-column foreign key indexes

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Redundant indexes with multi-column foreign key indexes

Keith Squier
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Redundant indexes with multi-column foreign key indexes

Fred Toussi-2
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

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user