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
tabl
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
--
> 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
: 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
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
r 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_situaca
> > 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
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
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 quer
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
qu
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 que
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,
diately 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:
> Sent: Tuesday, August 14, 2007 11:04 AM
> Subject: Problem with a c
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
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
15 matches
Mail list logo
