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`)
