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]
