Re: Problem with a complex query
Hello, I don't know how indexes work is something I hear often. I will try to answer in the form of a blog post when I have some time. It is too complex to really answer in a short form, but basically an index is a b-tree (look at wikipedia for a good explanation) built on top of the table. The indexed columns are present in each internal node, and the leaves are pointers to the rows in the table. Indexes let you find rows in log(#rows) number of operations instead of scanning the whole table. In the meantime, try reading this: http://dev.mysql.com/doc/en/query-speed.html There is actually a lot in the MySQL manual that explains how things work. Write back to the list with any questions you still have :) Baron Hugo Ferreira da Silva wrote: Hi, I've done some indexes in my tables and I solved my problem. But I still confused with indexes. I created one multiple index with the main 4 columns for mensagenspara's table. But it doesn't work. So, I created one index with 3 columns and one for each column, wich give me 4 indexes, and later, I created another one. I'm with 5 indexes in the end. Now my response time comes to 0.72 with users that have more than 7,000 messages sent (in mensagens table) and less than 0.1 with users that have around 1,000 messages sent. And in the end, the UNION works fine for me. I don't know how indexes work at all. Could someone explain? Thanks you all for your help. -- The query: explain (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 , usuarios u, usuarios up, mensagens m 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 = 545 GROUP BY m.codmensagem ) UNION ALL (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 , usuarios u, usuarios up, mensagens m 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 = 545) ORDER by dataenvio asc, horaenvio asc LIMIT 0,40 explain for this query: id: 1 select_type: PRIMARY table: u type: const possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using temporary; Using filesort --- id: 1 select_type: PRIMARY table: m type: ref possible_keys: PRIMARY,indice01,indice02,indice03,usuario_pasta_situacao key: usuario_pasta_situacao key_len: 15 ref: const,const,const rows: 4028 Extra: Using where --- id: 1 select_type: PRIMARY table: mp type: ref possible_keys: indice01,usuario_pasta_situacao,mensagem_idx key: mensagem_idx key_len: 5 ref: teste2.m.codmensagem rows: 3 Extra: Using where --- id: 1 select_type: PRIMARY table: up type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.mp.codusuario rows: 1 Extra: --- id: 2 select_type: UNION table: up type: const possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: const rows: 1 Extra: --- id: 2 select_type: UNION table: mp type: range possible_keys: indice01,indice02,indice03,usuario_pasta_situacao,mensagem_idx key: usuario_pasta_situacao key_len: 15 ref: rows: 2 Extra: Using where --- id: 2 select_type: UNION table: m type: eq_ref possible_keys: PRIMARY,indice01,usuario_pasta_situacao key: PRIMARY key_len: 4 ref: teste2.mp.codmensagem rows: 1 Extra: Using where --- id: 2 select_type: UNION table: u type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.m.codusuario rows: 1 Extra: --- id: select_type: UNION RESULT table: type: ALL possible_keys: key: key_len: ref: rows: Extra: Using filesort --- Indexes for mensagens: KEY `indice01` (`codusuario`), KEY `indice02` (`situacao`), KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`) Indexes for mensagenspara: KEY `indice01` (`codusuario`), KEY `indice02` (`situacao`), KEY `indice03` (`codpasta`), KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`), KEY `mensagem_idx` (`codmensagem`) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with a complex query
I solved a similar problem with a messaging system that was very slow because it was doing full table scan each time the query ran. I didn't have the chance to change the schema so this is what I came up with... SET @var = (SELECT MAX(message_id) FROM messages); SELECT columns... FROM message WHERE your cirteria = blah AND message_id BETWEEN @var - 1 AND @var This approach allowed me to elimiate the full table scan as we were only scanning the most recent 10, 000 rows. This assumes that everything you want falls within this range. It worked for us and maybe it will for you. -Original Message- From: Hugo Ferreira da Silva [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: 14 August 2007 19:19 To: mysql@lists.mysql.com Subject: Re: Problem with a complex query I've created an index with the statement create index `usuario_pasta_situacao` on `mensagens` (codusuario, codpasta, situacao); And for mensagenspara table this index create index `mensagem_usuario_pasta_situacao_idx` on `mensagenspara` (codmensagem, codusuario, codpasta, situacao); This is query 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 mensagens m, mensagenspara mp, usuarios u, 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 And this is what explain shows: id: 1 select_type: SIMPLE table: m type: ALL possible_keys: PRIMARY,usuario_pasta_situacao key: key_len: ref: rows: 68337 Extra: Using where; Using temporary; Using filesort --- id: 1 select_type: SIMPLE table: u type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.m.codusuario rows: 1 Extra: --- id: 1 select_type: SIMPLE table: mp type: ref possible_keys: mensagem_usuario_pasta_situacao_idx key: mensagem_usuario_pasta_situacao_idx key_len: 5 ref: teste2.m.codmensagem rows: 3 Extra: Using where --- id: 1 select_type: SIMPLE table: up type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.mp.codusuario rows: 1 Extra: --- I'm a little consufed with these indexes... could you please suggest a better way to create them? And again, thank you for your attention and patience :-) 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
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.
RE: Problem with a complex query
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]
Re: Problem with a complex query
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]
Re: Problem with a complex query
Hi, I've done some indexes in my tables and I solved my problem. But I still confused with indexes. I created one multiple index with the main 4 columns for mensagenspara's table. But it doesn't work. So, I created one index with 3 columns and one for each column, wich give me 4 indexes, and later, I created another one. I'm with 5 indexes in the end. Now my response time comes to 0.72 with users that have more than 7,000 messages sent (in mensagens table) and less than 0.1 with users that have around 1,000 messages sent. And in the end, the UNION works fine for me. I don't know how indexes work at all. Could someone explain? Thanks you all for your help. -- The query: explain (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 , usuarios u, usuarios up, mensagens m 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 = 545 GROUP BY m.codmensagem ) UNION ALL (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 , usuarios u, usuarios up, mensagens m 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 = 545) ORDER by dataenvio asc, horaenvio asc LIMIT 0,40 explain for this query: id: 1 select_type: PRIMARY table: u type: const possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using temporary; Using filesort --- id: 1 select_type: PRIMARY table: m type: ref possible_keys: PRIMARY,indice01,indice02,indice03,usuario_pasta_situacao key: usuario_pasta_situacao key_len: 15 ref: const,const,const rows: 4028 Extra: Using where --- id: 1 select_type: PRIMARY table: mp type: ref possible_keys: indice01,usuario_pasta_situacao,mensagem_idx key: mensagem_idx key_len: 5 ref: teste2.m.codmensagem rows: 3 Extra: Using where --- id: 1 select_type: PRIMARY table: up type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.mp.codusuario rows: 1 Extra: --- id: 2 select_type: UNION table: up type: const possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: const rows: 1 Extra: --- id: 2 select_type: UNION table: mp type: range possible_keys: indice01,indice02,indice03,usuario_pasta_situacao,mensagem_idx key: usuario_pasta_situacao key_len: 15 ref: rows: 2 Extra: Using where --- id: 2 select_type: UNION table: m type: eq_ref possible_keys: PRIMARY,indice01,usuario_pasta_situacao key: PRIMARY key_len: 4 ref: teste2.mp.codmensagem rows: 1 Extra: Using where --- id: 2 select_type: UNION table: u type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.m.codusuario rows: 1 Extra: --- id: select_type: UNION RESULT table: type: ALL possible_keys: key: key_len: ref: rows: Extra: Using filesort --- Indexes for mensagens: KEY `indice01` (`codusuario`), KEY `indice02` (`situacao`), KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`) Indexes for mensagenspara: KEY `indice01` (`codusuario`), KEY `indice02` (`situacao`), KEY `indice03` (`codpasta`), KEY `usuario_pasta_situacao` (`codusuario`,`codpasta`,`situacao`), KEY `mensagem_idx` (`codmensagem`)
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]
RE: Problem with a complex query
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
Re: Problem with a complex query
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
Re: Problem with a complex query
hum... I get it. But in my query, I look in 3 tables FROM mensagens m, mensagenspara mp, usuarios u, usuarios up WHERE m.codmensagem = mp.codmensagem AND u.codusaurio = m.codusuario AND up.codusuario = mp.codusuario m.codmensagem, u.codusaurio and up.codusuario are primary keys mp.codusuario, m.codusuario and mp.codmensagem are indexes. I'm joining the usuario's table twice to get the name of who is sending and who is receiving. I thought that creating indexes for the others columns will optimize the results, because I'm using them in where clause. Do you know some way to turn the response time of this query faster?
Re: Problem with a complex query
what does explain tell you about the query? I also think you should try a combined index with all three columns that are used in one index. As far as I know only one index can be used per query... B On Aug 14, 2007, at 1:26 PM, Hugo Ferreira da Silva wrote: hum... I get it. But in my query, I look in 3 tables FROM mensagens m, mensagenspara mp, usuarios u, usuarios up WHERE m.codmensagem = mp.codmensagem AND u.codusaurio = m.codusuario AND up.codusuario = mp.codusuario m.codmensagem, u.codusaurio and up.codusuario are primary keys mp.codusuario, m.codusuario and mp.codmensagem are indexes. I'm joining the usuario's table twice to get the name of who is sending and who is receiving. I thought that creating indexes for the others columns will optimize the results, because I'm using them in where clause. Do you know some way to turn the response time of this query faster? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Problem with a complex query
The indexes are the primary way of tuning your query speed but bear in mind that mysql can only use 1 index-per-table-per-query.. your single column indexes do help some. try an EXPLAIN to see what I mean: EXPLAIN SELECT ... This will show you how mysql is approaching the query. From your query, figure which attributes you are selecting on and then create an index on them with the smallest cardinality (the one with the fewest distinct values) going first. if I have a table with FIRSTNAME, LASTNAME, CITY, GENDER and I am searching on all LASTNAME and GENDER, I would create an index on (LASTNAME,GENDER) rather than (GENDER, LASTNAME) because the earlier terms should narrow the search as much as possible. - michael On 8/14/07, Hugo Ferreira da Silva [EMAIL PROTECTED] wrote: hum... I get it. But in my query, I look in 3 tables FROM mensagens m, mensagenspara mp, usuarios u, usuarios up WHERE m.codmensagem = mp.codmensagem AND u.codusaurio = m.codusuario AND up.codusuario = mp.codusuario m.codmensagem, u.codusaurio and up.codusuario are primary keys mp.codusuario, m.codusuario and mp.codmensagem are indexes. I'm joining the usuario's table twice to get the name of who is sending and who is receiving. I thought that creating indexes for the others columns will optimize the results, because I'm using them in where clause. Do you know some way to turn the response time of this query faster? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- - 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]
Re: Problem with a complex query
I've created an index with the statement create index `usuario_pasta_situacao` on `mensagens` (codusuario, codpasta, situacao); And for mensagenspara table this index create index `mensagem_usuario_pasta_situacao_idx` on `mensagenspara` (codmensagem, codusuario, codpasta, situacao); This is query 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 mensagens m, mensagenspara mp, usuarios u, 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 And this is what explain shows: id: 1 select_type: SIMPLE table: m type: ALL possible_keys: PRIMARY,usuario_pasta_situacao key: key_len: ref: rows: 68337 Extra: Using where; Using temporary; Using filesort --- id: 1 select_type: SIMPLE table: u type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.m.codusuario rows: 1 Extra: --- id: 1 select_type: SIMPLE table: mp type: ref possible_keys: mensagem_usuario_pasta_situacao_idx key: mensagem_usuario_pasta_situacao_idx key_len: 5 ref: teste2.m.codmensagem rows: 3 Extra: Using where --- id: 1 select_type: SIMPLE table: up type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.mp.codusuario rows: 1 Extra: --- I'm a little consufed with these indexes... could you please suggest a better way to create them? And again, thank you for your attention and patience :-)
Re: Problem with a complex query
From explain you can see that your problem lies within the mensagens table (the first entry from your explain query) where it says type: ALL and rows 68337. This basically means that it is not using any index for this table. MySQL doesn't seem to be very smart about queries involving OR and things like . For me creating temporary tables or writing perl scripts to do the job solved my particular problems. But I am working with tables that don't change but have some 100,000,000 rows... I guess I suggest, introducing some redundancy and removing the OR statement could help in your case... B On Aug 14, 2007, at 2:19 PM, Hugo Ferreira da Silva wrote: ype: ALL
Re: Problem with a complex query
MySQL doesn't seem to be very smart about queries involving OR and things like . For me creating temporary tables or writing perl scripts to do the job solved my particular problems. But I am working with tables that don't change but have some 100,000,000 rows... I guess I suggest, introducing some redundancy and removing the OR statement could help in your case... You are right, I see it now..the OR clause combining 2 distinct attribute clauses is killing you.. as the terms are different, no 1 index can simplify both directions so you wind up scanning the table in every case: I have had good luck breaking things like that up into 2 separate queries and then UNIONing them together... each branch of the UNION can plan it's own strategy. -- - 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]
