Select with Inner join too slow

Question

Select with Inner join too slow

Navigation

#1 by (6 votes)

2

I have a problem where select is bringing the right result, but it takes too long. Follow the 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

mysql sql join inner

asked by anonymous 05.01.2018 / 20:26

1 answer

Decrement the lines of the code while maintaining the function of getElementByID? Generate 9-digit keys with Python

score 6 · Accepted Answer

Dude you can take into account in your JOIN also the size of records of each table and make Join accordingly, for example:

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

Your join should start with the smaller tables, that is, for OUTRA_TABELA , then FINAFIM and last TDCUPANT . Why this?

Well, when the database reaches the last join table, most of the records will already be separated, filtered, taking less time inside the table with 900,000 records. Of course in your case does not help much, but should improve the performance a little.

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

What are indexes?

Indices are, roughly speaking, like the index of a book. It is for him that the database is oriented to find the records more quickly. Every database has this feature.

To create index in Firebird for example do this:

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] . Because it is unique ([b:b2b8d0c71d]UNIQUE[/b:b2b8d0c71d]) I can not include a vendor with the same [b:b2b8d0c71d]CNPJ, FANTASIA e RAZAO[/b:b2b8d0c71d] that already exists in the database.

You can create as many indexes as necessary for your database. But content is not just for this utility. Indexes help data integrity such as Checks, Constraints, and Foreign Keys. They help in sorting and etc.

source: link