A word of caution: before you just throw FORCE INDEX at it, study your explains very carefully.. Most of the answers to your specific questions are in there.. run it on a server with lots of data, you will get very different results between small datasets and very large ones.
- michael On 8/15/07, Rhys Campbell <[EMAIL PROTECTED]> wrote: > 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] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
