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
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,
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 i
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
13 matches
Mail list logo