List:General Discussion« Previous MessageNext Message »
From:Marc De Caluwé Date:April 13 1999 4:43pm
Subject:RE: Performance.
View as plain text  
Especially to Fred, Monty and Sasha (thanks for the answers already).

Monty,
I read the chapter and really don't see what the problem could be with the
indexes.


Fred,
EXPLAIN gives me

table                    type    possible_keys       key         key_len
ref                        rows          extra
LinkedInfo            ALL     NULL                   NULL       NULL
NULL                    618920
JoinedLinkedInfo   ref       Link_ID                Link_ID    8
LinkedInfo.Link_ID  10             where used

I'm doing this from the mysql> client.
The Link_ID fields have exactly the same type. Key_buffer and others are
like I can find them in the manual.



Sasha,
I don't see how I can rewrite it without LEFT JOIN. I tried to rewrite it as
(which should be faster I think):

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;

EXPLAIN fort this gives me

table                    type    possible_keys       key          key_len
ref                                  rows          extra
JoinedLinkedInfo   ref       Field_ID                Field_ID    8
???                                10
LinkedInfo            eq_ref  PRIMARY,Link_ID PRIMARY 8
JoinedLinkedInfo.Link_ID  1

When I try to execute this, I get the following error:

ERROR 1114: The table SQL33d2aa_0 is full.

Can anyone help?

Marc

-----Original Message-----
From: Fred Lindberg [mailto:lindberg@stripped]
Sent: vrijdag 9 april 1999 16:22
To: mysql@stripped
Subject: Re: Performance.


On Fri, 9 Apr 1999 15:59:27 +0200, Marc De Caluwé wrote:

>mySQL gives me the result (84.245 records) after... 2 hours and 59 minutes.
>
>The same query (on a similar pc) in Access takes about 6-7 minutes.
>What am I doing wrong???

Can you post table structure from MySQL (describe ...)? Are you doing
this from Access via OBDC, or from the mysql> client? Are the LINK_ID
fields of EXACTLY the same type? Is the Key_buffer big enough to hold
both indices? Other buffers?


-Sincerely, Fred

(Frederik Lindberg, Infectious Diseases, WashU, St. Louis, MO, USA)



---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread1584@stripped

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@stripped instead.



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