List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:August 16 2007 10:47am
Subject:Re: Problem with a complex query
View as plain text  
Hello,

"I don't know how indexes work" is something I hear often.  I will try 
to answer in the form of a blog post when I have some time.  It is too 
complex to really answer in a short form, but basically an index is a 
b-tree (look at wikipedia for a good explanation) built on top of the 
table.  The indexed columns are present in each internal node, and the 
leaves are pointers to the rows in the table.

Indexes let you find rows in log(#rows) number of operations instead of 
scanning the whole table.

In the meantime, try reading this:
http://dev.mysql.com/doc/en/query-speed.html

There is actually a lot in the MySQL manual that explains how things 
work.  Write back to the list with any questions you still have :)

Baron

Hugo Ferreira da Silva wrote:
> Hi,
> 
> I've done some indexes in my tables and I solved my problem.
> But I still confused with indexes. I created one multiple index with the
> main 4 columns for mensagenspara's table. But it doesn't work. So, I created
> one index with 3 columns and one for each column, wich give me 4 indexes,
> and later, I created another one. I'm with 5 indexes in the end. Now my
> response time comes to 0.72 with users that have more than 7,000 messages
> sent (in mensagens table) and less than 0.1 with users that have around
> 1,000 messages sent.
> 
> And in the end, the UNION works fine for me.
> I don't know how indexes work at all. Could someone explain?
> Thanks you all for your help.
> 
> --------------
> 
> The query:
> 
>  explain
> (SELECT
> m.codmensagem,
> m.codprioridade,
> m.codusuario,
> m.codmensagemoriginal,
> m.codmensagempai,
> m.assunto,
> m.dataenvio,
> m.horaenvio,
> m.datalimite,
> m.horalimite,
> m.anexo,
> m.tipo,
> u.nome,
> up.nome as nomepara,
> mp.codrespondida,
> mp.codmensagempara,
> mp.codusuario as codusuariopara,
> mp.situacao
> FROM
>  mensagenspara mp , usuarios u, usuarios up, mensagens m
> 
> WHERE
> m.ativo=1
> 
> AND mp.codmensagem = m.codmensagem
> AND u.codusuario = m.codusuario
> AND up.codusuario = mp.codusuario
> 
> AND m.codpasta = 2
> AND m.situacao = 1
> AND m.codusuario = 545
> 
> GROUP BY m.codmensagem
> )
> 
> UNION ALL
> 
> (SELECT
> m.codmensagem,
> m.codprioridade,
> m.codusuario,
> m.codmensagemoriginal,
> m.codmensagempai,
> m.assunto,
> m.dataenvio,
> m.horaenvio,
> m.datalimite,
> m.horalimite,
> m.anexo,
> m.tipo,
> u.nome,
> up.nome as nomepara,
> mp.codrespondida,
> mp.codmensagempara,
> mp.codusuario as codusuariopara,
> mp.situacao
> FROM
> 
>  mensagenspara mp , usuarios u, usuarios up, mensagens m
> 
> WHERE
> m.ativo=1
> 
> AND mp.codmensagem = m.codmensagem
> AND u.codusuario = m.codusuario
> AND up.codusuario = mp.codusuario
> 
> AND mp.codpasta = 2
> AND mp.situacao != 4
> AND mp.codusuario = 545)
> 
> ORDER by dataenvio asc, horaenvio asc
> LIMIT 0,40
> 
> 
> explain for this query:
> 
> id: 1
> select_type: PRIMARY
> table: u
> type: const
> possible_keys: PRIMARY,codusuario
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> Extra: Using temporary; Using filesort
> -------------------------------
> id: 1
> select_type: PRIMARY
> table: m
> type: ref
> possible_keys: PRIMARY,indice01,indice02,indice03,usuario_pasta_situacao
> key: usuario_pasta_situacao
> key_len: 15
> ref: const,const,const
> rows: 4028
> Extra: Using where
> -------------------------------
> id: 1
> select_type: PRIMARY
> table: mp
> type: ref
> possible_keys: indice01,usuario_pasta_situacao,mensagem_idx
> key: mensagem_idx
> key_len: 5
> ref: teste2.m.codmensagem
> rows: 3
> Extra: Using where
> -------------------------------
> id: 1
> select_type: PRIMARY
> table: up
> type: eq_ref
> possible_keys: PRIMARY,codusuario
> key: PRIMARY
> key_len: 4
> ref: teste2.mp.codusuario
> rows: 1
> Extra:
> -------------------------------
> id: 2
> select_type: UNION
> table: up
> type: const
> possible_keys: PRIMARY,codusuario
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> Extra:
> -------------------------------
> id: 2
> select_type: UNION
> table: mp
> type: range
> possible_keys:
> indice01,indice02,indice03,usuario_pasta_situacao,mensagem_idx
> key: usuario_pasta_situacao
> key_len: 15
> ref:
> rows: 2
> Extra: Using where
> -------------------------------
> id: 2
> select_type: UNION
> table: m
> type: eq_ref
> possible_keys: PRIMARY,indice01,usuario_pasta_situacao
> key: PRIMARY
> key_len: 4
> ref: teste2.mp.codmensagem
> rows: 1
> Extra: Using where
> -------------------------------
> id: 2
> select_type: UNION
> table: u
> type: eq_ref
> possible_keys: PRIMARY,codusuario
> key: PRIMARY
> key_len: 4
> ref: teste2.m.codusuario
> rows: 1
> Extra:
> -------------------------------
> id:
> select_type: UNION RESULT
> table:
> type: ALL
> possible_keys:
> key:
> key_len:
> ref:
> rows:
> Extra: Using filesort
> -------------------------------
> 
> Indexes for "mensagens":
> KEY `indice01` (`codusuario`),
> KEY `indice02` (`situacao`),
> KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`)
> 
> Indexes for "mensagenspara":
> KEY `indice01` (`codusuario`),
> KEY `indice02` (`situacao`),
> KEY `indice03` (`codpasta`),
> KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`),
> KEY `mensagem_idx` (`codmensagem`)
> 
Thread
Problem with a complex queryHugo Ferreira da Silva14 Aug
RE: Problem with a complex queryRhys Campbell14 Aug
Re: Problem with a complex queryHugo Ferreira da Silva14 Aug
Re: Problem with a complex queryHugo Ferreira da Silva14 Aug
  • Re: Problem with a complex queryBernd Jagla14 Aug
Re: Problem with a complex queryHugo Ferreira da Silva14 Aug
  • Re: Problem with a complex queryBernd Jagla14 Aug
Re: Problem with a complex queryMichael Dykman14 Aug
RE: Problem with a complex queryRhys Campbell15 Aug
  • Re: Problem with a complex queryHugo Ferreira da Silva15 Aug
RE: Problem with a complex queryRhys Campbell15 Aug
  • Re: Problem with a complex queryMichael Dykman15 Aug
    • Re: Problem with a complex queryHugo Ferreira da Silva15 Aug
      • Re: Problem with a complex queryBaron Schwartz16 Aug