Thanks for responding.
That's interesting. I will definitely give your sugestion a try. I thought
the two syntaxs had the same effect. What does the optimizer do
differently?
> -----Original Message-----
> From: Andrew Murphy [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 26, 2001 5:12 AM
> To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
> Subject: RE: Disk-bound joins
>
>
> Hi,
>
> Excuse me if this is wrong, but I think its the INNER JOIN which
> is slowing
> down the query.
>
> Wouldnt it be better (less memory intensive) to just use:
> "FROM zipwork1 as z, pctwork1 as p WHERE z.run_id = p.run_id"
> to join the tables if they are 1-1 related.
>
>
> Andrew Murphy
>
>
> -----Original Message-----
> From: Will French [mailto:[EMAIL PROTECTED]]
> Sent: 26 September 2001 7:23 am
> To: [EMAIL PROTECTED]
> Subject: Disk-bound joins
>
>
> Hi all,
>
> I have two very large tables (4 million recs each) where the records from
> each have a 1-to-1 relationship. They share a primary key (int) and there
> are no unmatched records in either table (i.e. it is a true 1-to-1). I am
> trying to combine a few columns from each of the two tables into
> a new third
> table. No matter what I do, the select statement process becomes
> disk-bound
> (low processor utilization despite no competition, tons of disk activity,
> excrutiatingly slow). Why is this? Has anyone else encountered this kind
> of trouble?
>
> I have gobs of memory (1.25Gb) and I am using the settings from
> my-huge.cnf.
> I have a reasonable processor - 733Mhz (not that its doing anything). I
> have a ATA-100 IDE hard disk, on a dedicated controller completely devoted
> to mysql. I am using mysql 3.23.41 on Win2k.
>
> My tables are as follows:
>
> CREATE TABLE zipwork1 (
> runid int not null primary key,
> zip char(5),
> plus4 char(4),
> addrerr tinyint
> ); /* This table has 4,014,438 records */
>
> CREATE TABLE pctwork1 (
> runid int not null primary key,
> cnty char(3) not null,
> precinct char(3) not null,
> cd tinyint,
> sd tinyint,
> hd tinyint
> ); /* This table also has 4,014,438 records */
>
> CREATE TABLE zippctmapwork (
> runid int not null,
> zip char(5),
> plus4 char(4),
> addrerr tinyint,
> cnty char(3),
> precinct char(3),
> cd tinyint,
> sd tinyint,
> hd tinyint
> ); /* This is the table I am trying to populate (starts out empty) */
>
> Here is my statement:
> INSERT INTO zippctmapwork (runid, zip, plus4, addrerr, cnty, precinct, cd,
> sd, hd)
> SELECT z.runid, z.zip, z.plus4, z.addrerr, p.cnty, p.precinct, p.cd, p.sd,
> p.hd
> FROM zipwork1 AS z INNER JOIN pctwork1 as p ON z.runid = p.runid;
>
> This is taking almost 20 minutes to execute. 20 minutes is great compared
> to when I try this on the 2 tables that zipwork1 and pctwork1 are
> originated
> from (zipwork1 and pctwork1 are subset tables I created to try and speed
> this thing up). When I ran it on the original tables, I had to kill it
> after 100 minutes and it had not even begun writing to the result
> table yet.
>
> Anyone have any ideas... anyone?
>
> Thanks in advance for your help
>
> Will French
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
