Struggling with the Portuguese here but... What kind of indexes do you have in place? Are the y appropriate?
I have had some success with removed or clauses from queries, creating a new query and join them with a UNION ALL. -----Original Message----- From: Hugo Ferreira da Silva [mailto:[EMAIL PROTECTED] Sent: 14 August 2007 16:04 To: mysql@lists.mysql.com Subject: Problem with a complex query Hi, I have a message system wich work in this way: - Each message can be sent to one or more recipients - Each message received have a lot of settings, like date and hour received, date and hour of read, mark as read and so on - When the user select "view message", the system will get the history for this message, with each answers of that message. The first table, messages, has about 56.000 rows, and the messages for table has about 200.000 rows. The problem is when the user goes to messages list. Because the message could be moved to any folder, I have to query in "messages" table and "messages for" table, to check if in the selected folder exists messages sent or received. Below is the tables structures of my system. Users table (called usuarios, in portuguese) CREATE TABLE `usuarios` ( `codusuario` int(11) NOT NULL AUTO_INCREMENT, `codexterno` varchar(11) DEFAULT NULL, `codgrupo` smallint(6) DEFAULT NULL, `nome` varchar(50) DEFAULT NULL, `login` varchar(50) DEFAULT NULL, `senha` varchar(50) DEFAULT NULL, `senhacriptografada` varchar(50) DEFAULT NULL, `tamanhoarquivos` int(11) DEFAULT NULL, `cronos` int(11) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `ativo` int(11) DEFAULT NULL, PRIMARY KEY (`codusuario`), UNIQUE KEY `codusuario` (`codusuario`) ) ENGINE=MyISAM AUTO_INCREMENT=931 DEFAULT CHARSET=latin1 Messages table (mensagens) CREATE TABLE `mensagens` ( `codmensagem` int(11) NOT NULL DEFAULT '0', `codprioridade` int(11) DEFAULT NULL, `assunto` varchar(150) DEFAULT NULL, `assuntoconsulta` varchar(150) DEFAULT NULL, `msg` text, `codusuario` int(11) DEFAULT NULL, `dataenvio` int(11) DEFAULT NULL, `horaenvio` int(11) DEFAULT NULL, `situacao` int(11) DEFAULT NULL, `datalimite` int(11) DEFAULT NULL, `horalimite` int(11) DEFAULT NULL, `anexo` int(11) DEFAULT NULL, `codmensagemoriginal` int(11) DEFAULT NULL, `codmensagempai` int(11) DEFAULT NULL, `codpasta` int(11) DEFAULT NULL, `tipo` int(11) DEFAULT NULL, `dhdatatarefa` int(11) DEFAULT NULL, `dhhoratarefa` int(11) DEFAULT NULL, `repvalor` int(11) DEFAULT NULL, `repperiodo` int(11) DEFAULT NULL, `repnvezes` int(11) DEFAULT NULL, `repdataterminar` int(11) DEFAULT NULL, `avvalor` int(11) DEFAULT NULL, `avtipo` int(11) DEFAULT NULL, `avdata` int(11) DEFAULT NULL, `avhora` int(11) DEFAULT NULL, `alarmetipo` int(11) DEFAULT NULL, `alarmevalor` int(11) DEFAULT NULL, `ativo` int(11) DEFAULT NULL, `permiteresposta` int(11) DEFAULT NULL, PRIMARY KEY (`codmensagem`), KEY `codusuario` (`codusuario`,`codpasta`,`codmensagem`), KEY `dataenvio` (`dataenvio`,`horaenvio`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Messages for table (mensagenspara) CREATE TABLE `mensagenspara` ( `codmensagempara` int(11) NOT NULL DEFAULT '0', `codmensagem` int(11) DEFAULT NULL, `codusuario` int(11) DEFAULT NULL, `situacao` int(11) DEFAULT NULL, `codvencimento` int(11) DEFAULT NULL, `datarecebimento` int(11) DEFAULT NULL, `horarecebimento` int(11) DEFAULT NULL, `dataleitura` int(11) DEFAULT NULL, `horaleitura` int(11) DEFAULT NULL, `codpasta` int(11) DEFAULT NULL, `codrespondida` int(11) DEFAULT NULL, `alarmedata` int(11) DEFAULT NULL, `alarmehora` int(11) DEFAULT NULL, `alarmetempo` int(11) DEFAULT NULL, `alarmetipo` int(11) DEFAULT NULL, `concluidodata` int(11) DEFAULT NULL, `concluidohora` int(11) DEFAULT NULL, `concluidomsg` varchar(255) DEFAULT NULL, `tipo` int(11) DEFAULT NULL, `avisado` int(11) DEFAULT NULL, `notificatarefanaocumprida` int(11) DEFAULT NULL, `notificatarefa` int(11) DEFAULT NULL, PRIMARY KEY (`codmensagempara`), KEY `codmensagempara` (`codmensagempara`,`codusuario`,`codpasta`,`codmensagem`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The SQL used : 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 cronosweb.mensagens m, cronosweb.mensagenspara mp, acessos.usuarios u, acessos.usuarios up WHERE m.ativo=1 AND m.codmensagem=mp.codmensagem AND m.codusuario=u.codusuario AND mp.codusuario=up.codusuario AND ( ( m.codpasta=1 AND m.codusuario = 916 AND m.situacao <> 0 ) OR ( mp.codpasta=1 AND mp.codusuario = 916 AND mp.situacao <> 4 ) ) GROUP BY m.codmensagem ORDER BY m.dataenvio DESC, m.horaenvio DESC, m.codmensagem DESC LIMIT 0,20 The time of this query takes to executes is about 13 ~ 15 seconds. I've tried with sub-selects (and also tried with derived), table partitioning and many also possibilities that my mind could imagine (I'm work this issue for 5 days) Using 5.1.20-beta-community-nt-debug. Thanks in advanced. -- Hugo Ferreira da Silva Programador Fone: (45) 9102-1148 http://www.hufersil.com.br [EMAIL PROTECTED] 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]
