1

Let's say we have a PRODUCT table, ORDER table and a junction table PRODUCT_ORDER.

I create a junction table with the following script:

CREATE TABLE public."PRODUCT_ORDER" (

    product_id bigint NOT NULL,
    order_id bigint NOT NULL,

    CONSTRAINT "PRODUCT_ORDER_PK" PRIMARY KEY (product_id, order_id),

    CONSTRAINT "FK_TO_PRODUCT" FOREIGN KEY (product_id)
        REFERENCES public."PRODUCT" (id) ON DELETE CASCADE,

    CONSTRAINT "FK_TO_ORDER" FOREIGN KEY (order_id)
        REFERENCES public."ORDER" (id) ON DELETE CASCADE
);

Also an index for the PK was created automatically:

CREATE UNIQUE INDEX "PRODUCT_ORDER_PK" ON public."PRODUCT_ORDER" USING btree (product_id, order_id)

It is expected that there will be practically only read operations for these tables and I would like to index the junction table intelligently.

Usually, I additionally create an index for foreign keys manually, e.g. for one-to-many relationship, like this:

CREATE INDEX "index_name" ON schema_name."table_name" (fk_column_name);

My question is: Do I need to create two indexes for foreign keys in addition to PK-index like this:

 CREATE INDEX "FK_TO_PRODUCT" ON public."PRODUCT_ORDER" (product_id);
 CREATE INDEX "FK_TO_ORDER" ON public."PRODUCT_ORDER"(order_id);

Or is it unnecessary, meaningless and the index for the PK will be enough?

In general, I'm interested in how to intelligently/correctly index the junction tables for many-to-many relationship?

If you need any clarification, let me know and thanks in advance!

Georgii Lvov
  • 1,771
  • 1
  • 3
  • 17
  • Useful link: https://stackoverflow.com/questions/571309/how-to-properly-index-a-linking-table-for-many-to-many-connection-in-mysql – Georgii Lvov Sep 20 '21 at 09:21

1 Answers1

2

No, you do not need to create two additional keys. Your primary key index will work for the first key (product_id) as well as the pair.

You need one additional index on order_id.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thank you very much for your answer, +1 ! Could you please explain, why primary key index will work for the first key as well as the pair, but not for the other `order_id` key? – Georgii Lvov Sep 19 '21 at 19:44
  • 1
    @GeorgeLvov . . . That is how (b-tree) indexes work. An index conceptually stores the data sorted by the keys, so if the index is sorted by `key1` then `key2`, it is sorted by `key1`. I happen to think the MySQL documentation on this subject does a decent job of explaining this (https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html). This functionality is similar across databases. – Gordon Linoff Sep 19 '21 at 22:24
  • 2
    Think of a phone book, it is a two column index on (last lane, first name). It can be used to efficiently search for people by last name, but not by first name. Order matters! – Laurenz Albe Sep 20 '21 at 02:59
  • Got it, thank you all! – Georgii Lvov Sep 20 '21 at 08:57