Jump to content
nunopicado

Optimização de Query

Recommended Posts

nunopicado

Boas pessoal

Tenho aqui uma query que funciona para o que eu quero. No entanto, é um bocado lenta (cerca de 2 segundos), e com tendêmcia a piorar.

SELECT *, EntidadeID AS EntID
,(SELECT Count(DocID) FROM Documentos INNER JOIN Counters ON Counters.CounterID = Documentos.CounterID WHERE EntidadeID = EntID AND TipoDoc=1) AS DocsCliente
,(SELECT Count(DocID) FROM Documentos INNER JOIN Counters ON Counters.CounterID = Documentos.CounterID WHERE EntidadeID = EntID AND TipoDoc=2) AS DocsFornecedor 
FROM Entidades 
ORDER BY Nome

Temos no fundo três tabelas, uma com uma lista de entidades (Entidades), outra com uma lista de documentos (Documentos), sendo a chave o campo EntidadeID, comum a ambas, e ainda uma terceira (Counters) com algumes características de cada documento, sendo a chave o campo CounterID.

O que eu preciso é uma lista de entidades dos tipos Cliente e Fornecedor (campos respectivos = 1) acrescida de dois campos, DocsCliente e DocsFornecedor, que indicarão respectivamente quantos documentos com o TipoDoc=1 ou TipoDoc=2 (campo este que está na tabela Counters).

Usei o método das subquerys, para calcular o nº de documentos.

Haverá algum método mais rápido?

Obrigado

PS: Eu sei que 2 segundos não é muito, agora. Mas a tendencia é de piorar, e daqui por 6 meses, preve-se que suba para mais do dobro.

Se puder evitar, melhor! ;)

Edited by nunopicado

"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Share this post


Link to post
Share on other sites
brunoais

Experimenta usar o EXPLAIN para cada uma das queries e depois um explain para isso tudo junto.

Dá para confirmar que elas são todas simples?

Além disso, qual é o método que o DBMS está a usar para fazer isso? Será que está realmente a ir buscar os dados duas vezes?

Que indices tens? Já tens um indice que abrange EntidadeID e TipoDoc (e não um para cada um).

Já tens chave estrangeira, mas parece que faltam outras para melhorar a velocidade.

Edited by brunoais
  • Vote 1

"[Os jovens da actual geração]não lêem porque não envolve um telecomando que dê para mirar e atirar, não falam porque a trapalhice é rainha e o calão é rei" autor: thoga31

Life is a genetically transmitted disease, induced by sex, with death rate of 100%.

Share this post


Link to post
Share on other sites
nunopicado

Pois, tabelas ainda mais simples... Assim como o meu conhecimento de MySQL! ;)

Nunca tinha usado o EXPLAIN. Usei agora e o resultado foi:

| *id* |   *select_type*    |   *table*   | *type* | *possible_keys* |  *key*  | *key_len* |               *ref*               | *rows* |    *Extra*     |
|   1  |      PRIMARY       |  Entidades  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |   56   | Using filesort |
|   3  | DEPENDENT SUBQUERY | Documentos  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |  4411  |  Using where   |
|   3  | DEPENDENT SUBQUERY |  Counters   |  ref   |     PRIMARY     | PRIMARY |    1      | ampos_treino.Documentos.CounterID |    1   |  Using where   |
|   2  | DEPENDENT SUBQUERY | Documentos  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |  4411  |  Using where   |
|   2  | DEPENDENT SUBQUERY |  Counters   |  ref   |     PRIMARY     | PRIMARY |    1      | ampos_treino.Documentos.CounterID |    1   |  Using where   |

Não tenho Foreign Keys, e quanto ao método, não percebi ao que te referes. Tipo de tabelas?

Se sim, são MyISAM.

Só estou a usar chaves primárias (Entidades -> EntidadeID; Documentos -> DocID; Counters -> CounterID)

A estrutura é:

- Cada Entidade pode ter vários Documentos

- Cada Documento só pode ter 1 Counter

- Cada Counter pode ter vários Documentos


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Share this post


Link to post
Share on other sites
nunopicado

Experimentei o seguinte:

- Mudei para InnoDB as tabelas Counters, Documentos e Entidades

- Criei uma Foreign Key no campo CounterID entre Counters e Documentos

A performance já melhorou, devolvendo de forma praticamente instantanea os dados que eu preciso. (e esta heim?).

Perguntas:

- Há alguma possibilidade de a coisa correr mal, fazendo esta alteração numa base de dados de produção. O risco de perda de dados existe?

- Que campos são elegiveis para Foreign Key?

Tentei criar uma para o EntidadeID e ele mandam-me apanhar morangos.


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Share this post


Link to post
Share on other sites
brunoais

O problema era muito simples. Falta de keys propriadas.

Ele usava as keys para ajudar, mas não estava a usar exclusivamente as keys.

Nunca tinha usado o EXPLAIN. Usei agora e o resultado foi:

Olha... aprende. É a ferramenta nº 1 qd se tem SELECTs que são lentos.

| *id* |   *select_type*    |   *table*   | *type* | *possible_keys* |  *key*  | *key_len* |               *ref*               | *rows* |    *Extra*     |
|   1  |      PRIMARY       |  Entidades  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |   56   | Using filesort |
|   3  | DEPENDENT SUBQUERY | Documentos  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |  4411  |  Using where   |
|   3  | DEPENDENT SUBQUERY |  Counters   |  ref   |     PRIMARY     | PRIMARY |    1      | ampos_treino.Documentos.CounterID |    1   |  Using where   |
|   2  | DEPENDENT SUBQUERY | Documentos  |  ALL   |      _NULL_     | _NULL_  |  _NULL_   |              _NULL_               |  4411  |  Using where   |
|   2  | DEPENDENT SUBQUERY |  Counters   |  ref   |     PRIMARY     | PRIMARY |    1      | ampos_treino.Documentos.CounterID |    1   |  Using where   |

Não tenho Foreign Keys, e quanto ao método, não percebi ao que te referes. Tipo de tabelas?

Se sim, são MyISAM.

Estava directamente relacionado com o EXPLAIN, mas isto acabou por ser informação interessante, mesmo.

MyISAM é uma tabela muito boa para uso temporário e em situações específicas para indices FULLTEXT. Fora isso, já não tem interesse.

Experimentei o seguinte:

- Mudei para InnoDB as tabelas Counters, Documentos e Entidades

- Criei uma Foreign Key no campo CounterID entre Counters e Documentos

A performance já melhorou, devolvendo de forma praticamente instantanea os dados que eu preciso. (e esta heim?).

Qd a velocidade é demasiado pequena, a 1ª coisa que se pensa é sempre se os indices estão correctos para o uso que a DB está a ter.

E, pelo que sei, ainda podias melhorar isso mais se usasses indices de 2 colunas para essa pesquisa específica

Perguntas:

- Há alguma possibilidade de a coisa correr mal, fazendo esta alteração numa base de dados de produção. O risco de perda de dados existe?

Mínimo. Se tiver muitos dados, mudar para InnoDB demora muito tempo (YMMV). O risco de perda não é maior que o que existe no dia-a-dia.

Há a possibilidade de perder indices específicos como indices FULLTEXT.

- Que campos são elegiveis para Foreign Key?

Tentei criar uma para o EntidadeID e ele mandam-me apanhar morangos.

Para explicar, nada melhor que o próprio manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Já agora, faz o exemplo e evita fazer multiple post.

  • Vote 1

"[Os jovens da actual geração]não lêem porque não envolve um telecomando que dê para mirar e atirar, não falam porque a trapalhice é rainha e o calão é rei" autor: thoga31

Life is a genetically transmitted disease, induced by sex, with death rate of 100%.

Share this post


Link to post
Share on other sites
nunopicado

Obrigado pelas dicas

Vou dar uma olhada aos detalhes.

Já agora, dizes que podia usar indices de 2 colunas?


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Share this post


Link to post
Share on other sites
brunoais

Vou dar uma olhada aos detalhes.

Details do matter!

(~= os pormenores são importantes)

Já agora, dizes que podia usar indices de 2 colunas?

É mais um "devias" do que um "podias".

Aliás, se n tens queries que usam só uma dessas colunas, então n vale a pena teres esse indice e deves usar indices de várias colunas.

You gotta be smart and pay attention to those tham details!

(~= Há que ser inteligente e tomar atenção aos pormenores!)


"[Os jovens da actual geração]não lêem porque não envolve um telecomando que dê para mirar e atirar, não falam porque a trapalhice é rainha e o calão é rei" autor: thoga31

Life is a genetically transmitted disease, induced by sex, with death rate of 100%.

Share this post


Link to post
Share on other sites
nunopicado

lol

O inglês eu percebo... O SQLês é que vou aprendendo conforme a necessidade! ;)


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Share this post


Link to post
Share on other sites
brunoais

Escrevo a versão em português pq faz parte. Isto é um fórum de uma comunidade portuguesa...

  • Vote 1

"[Os jovens da actual geração]não lêem porque não envolve um telecomando que dê para mirar e atirar, não falam porque a trapalhice é rainha e o calão é rei" autor: thoga31

Life is a genetically transmitted disease, induced by sex, with death rate of 100%.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.