First of all, thanks for your attention guys,
Rhys, I've tried UNION and UNION ALL statements too, but I found some
problems when ordering the results. I've tried order in each query and at
end - (SELECT ...) UNION (SELECT ...) ORDER BY ... -, and result was poorly
than this query.
Martin, that indexes names MySQL was created automatically when I created
the indexes, because I didn't specified an index name in alter table.
I create now one index for each column that I use in this query (and
correctly specified the index name - I guess).
Below is create statement now for "mensagespara":
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 `codmensagem_idx` (`codmensagem`),
KEY `codpasta_idx` (`codpasta`),
KEY `codusaurio_idx` (`codusuario`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I created in the others tables too.
Now my response time fall to ~ 6 seconds, but still too slow, because we
have around 300 employees using this system at same time, and in these tests
I'm using only my computer.
2007/8/14, Martin Gainty <[EMAIL PROTECTED]>:
>
> Hello Hugo
> In your last CREATE TABLE statement you have
> PRIMARY KEY (`codmensagempara`), --correctly define column codmensagepara
> to
> be used as a PRIMARY_LEY for this table
> the name of this index will be called 'PRIMARY'
>
> then following the convention from the doc
> {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
> later on you define an index with the same name but no index_type
> KEY `codmensagempara`
> (`codmensagempara`,`codusuario`,`codpasta`,`codmensagem`)
> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
>
> I am trying to understand the reason to use the SAME name to specify the
> PRIMARY_KEY column `codmensagempara` int(11) NOT NULL DEFAULT '0', from
> mensagempara
>
> and then later define a NEW key with the SAME name as the column
> KEY `codmensagempara`
> (`codmensagempara`,`codusuario`,`codpasta`,`codmensagem`)
>
> The rule is that as long as you do joins on unique indexed columns
> (columns
> which are defined by PRIMARY_KEY) you will always have fast queries
> e.g.
> SELECT * from TABLE1 t1, TABLE2 t2
> where t1.index_column==t2.index.column
> /********guaranteed to be fastest as these indexes are unique and will
> never
> cause FTS*********/
>
> M--
> This email message and any files transmitted with it contain confidential
> information intended only for the person(s) to whom this email message is
> addressed. If you have received this email message in error, please
> notify
> the sender immediately by telephone or email and destroy the original
> message without making a copy. Thank you.
>
> ----- Original Message -----
> From: "Hugo Ferreira da Silva" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Tuesday, August 14, 2007 11:04 AM
> 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]
> >
>
>
--
Hugo Ferreira da Silva
Programador
Fone: (45) 9102-1148
http://www.hufersil.com.br
[EMAIL PROTECTED]
