List:General Discussion« Previous MessageNext Message »
From:Marc De Caluwé Date:April 18 1999 12:58pm
Subject:RE: Performance.
View as plain text  
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?

Marc


Just to recall your memory:
Two tables:
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
LinkedInfo.Record_ID;
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
two).
-----Original Message-----
From: Michael Widenius [mailto:monty@stripped]
Sent: dinsdag 13 april 1999 22:34
To: Marc De Caluwé
Subject: RE: Performance.



<cut>

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?

Hi!


Please check the mail archive and the reference manual before posting!
You can find a solution to this problem at

http://www.tcx.se/Manual_chapter/manual_toc.html

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:

http://www.tcx.se/Manual_chapter/manual_Questions.html#Asking_questions

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.

Yours,
Monty

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.

Thread
Performance.Marc De Caluwé9 Apr
Re: Performance.Fred Lindberg9 Apr
  • RE: Performance.Marc De Caluwé13 Apr
    • Re: Performance.Sasha Pachev13 Apr
RE: Performance.Marc De Caluwé18 Apr
  • RE: Performance.Michael Widenius19 Apr