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
