Thanks, I agree I should have read the manual better. The new query gives me
the same results in about 16 minutes.
Though I still keep sitting here with two big questions:
1. In my eyes the two queries are quite similar. But the second one gives me
the results in 1/12th of the time! I would like to understand this. I tried
different things with EXPLAIN but I don't get an explanation for such
diffrent results. Can somenone explain how the optimizer exactly works?
2. I was looking for MySQL because I had some problems with the time Access
needed to join big tables. When I was looking at the performance sheets at
your site, MySQL seemed to be the solution. Although generally speaking I
really like MySQL, I'm just wondering if the perfomance on these big joins
is NOT on these sheets?
Anyone has an answer?
Just to recall your memory:
One is called LinkedInfo: 600.000 records, 8 columns with indexes on
Record_ID and Link_ID.
The other one JoinedLinkedInfo: about 2 million records, 6 columns with
indexes on Link_ID, Field_ID and ValueNum.
SELECT SUM(JoinedlinkedInfo.ValueNum) AS Amount, LinkedInfo.Record_ID FROM
(LinkedInfo LEFT JOIN JoinedLinkedInfo ON LinkedInfo.Link_ID =
JoinedLinkedInfo.Link_ID) WHERE (JoinedLinkedInfo.Field_ID=21) GROUP BY
Takes about 3 hours.
SELECT Sum(JoinedLinkedInfo.ValueNum) AS Amount, LinkedInfo.Record_ID FROM
JoinedLinkedInfo LEFT JOIN LinkedInfo USING (Link_ID) WHERE
JoinedLinkedInfo.Field_ID=21 GROUP BY LinkedInfo.Record_ID;
Takes 16 minutes.
In Access both queries take about 10 minutes (no difference between the
From: Michael Widenius [mailto:monty@stripped]
Sent: dinsdag 13 april 1999 22:34
To: Marc De Caluwé
Subject: RE: Performance.
Marc> When I try to execute this, I get the following error:
Marc> ERROR 1114: The table SQL33d2aa_0 is full.
Marc> Can anyone help?
Please check the mail archive and the reference manual before posting!
You can find a solution to this problem at
Search after 'is full'.
If you can't find solve your problem with the above information,
please post this question again and say where you have tried to locate
the answer to your question and what you did.
You can find information how to do a correct bug report at:
PLEASE read the above before reposting your question, this will save us all
a lot of time!
If you really need fast explicit help, you should consider taking
MySQL email support. We try to help everyone but our recourses are
quite limited and we must prioritize paying customers.
PS: This is a standard reply to questions that can easily be found in
the mail archive, the reference manual, the perl man pages or in
any of the examples in the MySQL distribution.