First, I would run just the select part with an explain in front of it to see what MySQL is trying to do. I've had MySQL run a query for an inordinate amount of time on a fairly small data set because of a typo and no index in use for a join.
Second, I would check the size of the data file to see if it is growing. At least you'll be able to see if something is going on.
Third, I would try just the select part with a limit of say 10 or 20. This will show you how long MySQL is taking to do the search and joins, eliminating data transfer time.
You need to find where it's bottlenecking. It may very well be that you are I/O bound (iostat). MySQL is reading from the tables, certainly using temporary tables with a data set of that size and trying to load data into a new table. That's a lot of reads and writes going on at once, your disk(s) may be getting hammered, especially if you are not using RAID.
You could also try disabling indexes on table 3 (if you have any) and then enabling after the import is done. That will speed up the import process.
Lastly, instead of doing and insert select, just select to a file (INTO OUTFILE). Then import the data using load data infile. A two step processing, but something that will allow you to control all the I/O that's going on.
On Apr 26, 2005, at 9:22 AM, Ed Sweeney wrote:
I have been trying to run an fairly large INSERT into an empty table joining two other tables now for several weeks and have not been able to get the query to run to completion even when sub-seting the data into smaller ranges.
I have tried this at MySQL releases 4.1.8a and 4.1.10a with no noticable improvement.
The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20.
The server is dedicated to MySQL. The my.cnf file is attached.
There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 processors).
SHOW STATUS shows very good buffer hit ratio. Current stats are attached. Server was re-booted last Friday. It has been running the INSERT query since Saturday mid-morning and has yet to complete.
Table 1 230 million rows total compund PK index – 4 columns range 1 should select 35 million rows. Explain plan shows it to be using the PK
Table 2 598 million rows compound PK index – 4 columns and one secondary index. Range 1 should select about 130 million rows
Table 3 Empty table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this table
The general form of the query is:
INSERT INTO Table 3 (col1, …. Col18)
SELECT
Col1,,.col18
FROM
Table 1 a
INNER JOIN Table 2 b ON (PK columns and range selection)
WHERE
a.col5 = b.col5
AND
….
AND
…
AND
a.col18 = b.col18
Any suggestions are welcome.
Ed Sweeney
<show_status.txt>--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
