What Index should be created for this query?

Question

So I need help to figured it out what Index(es) I need to create to improve the performance of this query:

SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F, produto P
WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta';

The tables fornecedor and produto are created this way:

Fornecedor

create table fornecedor (
nif char(9) not null unique,
nomef varchar(80) not null,
constraint nif_size CHECK (Length(nif)=9),
constraint pk_fornecedor primary key(nif));

Produto

create table produto (
ean char(13) not null unique,
design varchar(255) not null,
categoria varchar(80) not null,
forn_primario char(9) not null,
data date not null,
constraint ean_size CHECK (Length(ean)=13),
constraint pk_produto primary key(ean),
constraint fk_produto_categoria foreign key(categoria) references categoria(nome) ON DELETE CASCADE,
constraint fk_produto_fornecedor foreign key(forn_primario) references fornecedor(nif));

I dont know if I need to create a Multiple-Key Index like:

CREATE INDEX multiple_idx ON produto (forn_primario,categoria);

or something else???

do you know the different values you can have for `producto.categoria` ? if you can create an enum and index it or if the number of values for that column is huge and/or unknown you can have a table to all the categorias and a foreign key on the pk of that table from producto. — Akli REGUIG, Dec 08 '17 at 20:18
Before anything I can advice to **[NOT USE IMPLICIT JOINS](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins)** that way you get your joins out of the `WHERE`clause and don't mix it with your filters — jean, Dec 13 '17 at 16:24

score 0 · Answer 1 · answered Dec 08 '17 at 20:14

You definitely need an index starting with categoria. The best will be with forn_primario:

 create index ix_producto_categoria on producto(categoria, forn_primario)

to further support your query you may want to create

 create index ix_fornecedor_nif on fornecedor(nif, nomef)

score 0 · Answer 2 · answered Dec 08 '17 at 20:14

Your query is better written as:

SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F JOIN
     produto P
     ON F.nif = P.forn_primario AND P.categoria = 'Fruta';

For this version, you want indexes on product(categoria, forn_primario) and fornecdor(nif).

Then, if nif/nomef is unique in the first table, it should be expressed as:

SELECT F.nif, F.nomef
FROM fornecedor F 
WHERE EXISTS (SELECT 1
              FROM produto P
              WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta'
             );

For this version, you want an index on produto(forn_primario, categoria).

score 0 · Answer 3 · answered Dec 13 '17 at 16:11

0

On fornecedor, the attribute nif is a primary key... i don't think you should use an index on a PK

answered Dec 13 '17 at 16:11

Miki Bakaiki

13
6

What Index should be created for this query?

3 Answers3