Does the other user_id have a lot of messages. I think MySQL will choose to table ignore the index if the retrieved rows are above 30% of the table total.
Have you tried FORCE INDEX? -----Original Message----- From: Hugo Ferreira da Silva [mailto:[EMAIL PROTECTED] Sent: 15 August 2007 13:35 To: mysql@lists.mysql.com Subject: Re: Problem with a complex query I found something weird. This is my query now -------------------------- (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, mensagens m, usuarios u, usuarios up 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 = <USER_CODE>) UNION (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 mensagens m,mensagenspara mp, usuarios u, usuarios up 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 = <USER_CODE> GROUP BY m.codmensagem ) ORDER BY dataenvio DESC, horaenvio DESC, codmensagem DESC LIMIT 0,40 --------------------------- But depending on what USER_CODE I use, it found or not and index. For example, if I use my code, 916: id: 2 select_type: UNION table: mp type: ref possible_keys: usuario_mensagem_situacao key: usuario_mensagem_situacao key_len: 10 ref: teste2.up.codusuario,teste2.m.codmensagem rows: 1 Extra: Using where But if I use any other code: id: 2 select_type: UNION table: mp type: ALL possible_keys: usuario_mensagem_situacao key: key_len: ref: rows: 197980 Extra: Someone could explain this behavior and how I can fix? Thanks in advance. This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
