Select with Inner June too slow

Question

I have a problem where select is bringing the right result, but taking too long. Follow select :

SELECT c.cod_paciente,
       p.nome,
       i.valor,
       i.quantidade,
       d.convenio,
       c.cod_conta
FROM caddadosclinico d
INNER JOIN cadcontapct c ON d.cod_paciente = c.cod_paciente
INNER JOIN cadcontitens i ON i.cod_conta = c.cod_conta
INNER JOIN cadmatmed m ON i.cod_matmed = m.cod_matmed
INNER JOIN cadpaciente p ON p.cod_paciente = c.cod_paciente
WHERE m.grupo = '1'
  AND c.dt_conta_aberta >= STR_TO_DATE('$dt_init', '%d/%m/%Y')
  AND c.dt_conta_aberta <= STR_TO_DATE('$dt_final', '%d/%m/%Y')
GROUP BY c.cod_paciente
ORDER BY p.nome

Answer 1

Guy you can take into account in your JOIN also the size of records of each table and do the Join according to this, for example:

TDCUPANT = 900.000 registros
FINAFIM = 300.000 registros
OUTRA_TABELA = 1.000 registros

Your Join should start with the smaller tables, i.e., OUTRA_TABELA, afterward FINAFIM and lastly TDCUPANT. Why is that?

Well, by the time the bank reaches Join’s last table, most of the records will already be separate, filtered taking less time inside the table with 900,000 records. Of course in your case does not help much, but should improve a little the performance.

You can also create indexes for the fields involved in the [i:19c2cdec39]where[/i:19c2cdec39] e no [i:19c2cdec39]order by[/i:19c2cdec39].

What are the indexes?

The indexes are, roughly speaking, like the index of a book. This is why the database is oriented to find the records faster. Every database has this resource.

To create index in Firebird for example do this way:

CREATE UNIQUE INDEX NOME_DO_INDEX ON FORNECEDORES (CNPJ,FANTASIA,RAZAO)

Here, I’m creating a unique index with the name

[b:b2b8d0c71d]NOME_DO_INDEX[/b:b2b8d0c71d], na tabela [b:b2b8d0c71d]FORNECEDORES [/b:b2b8d0c71d]com os campoas [b:b2b8d0c71d]CNPJ, FANTASIA e RAZAO[/b:b2b8d0c71d]. For being unique ([b:b2b8d0c71d]UNIQUE[/b:b2b8d0c71d]) I cannot include a supplier with the same [b:b2b8d0c71d]CNPJ, FANTASIA e RAZAO[/b:b2b8d0c71d] which already exists in the database.

You can create as many indexes as necessary for your database. But index is not only for this utility. Indexes help data integrity like Checks, Constraints and Foreign Keys. Help in sorting and etc.

source: https://www.devmedia.com.br/forum/melhorar-o-desempenho-de-um-select-com-inner-join/31085