From: Rhys Campbell (rhys.campbelltouchlocal.com)
Date: Wed Aug 15 2007 - 08:11:34 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:hufersilgmail.com]
Sent: 15 August 2007 13:35
To: mysqllists.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 postmastertouchlocal.com. 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]