Martin,
1) I would insure there is an index on both TABLEY2.y_id and Y_TABLE.y_id
2) I would change the subselect in your insert statement to be a LEFT
JOIN... WHERE xxx IS NULL.
INSERT INTO X(column1)
SELECT Y.y_id
FROM Y_TABLE Y
LEFT JOIN TABLEY2 t2
ON t2.y_id = Y.y_id
WHERE t2.y_id IS NULL;
2a) If you MUST keep the subselect (due to you reasons beyond your control)
change
Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
to
NOT EXISTS(select 1 FROM TABLEY2 y2 where y2=Y.y_id);
3) It may be faster in some circumstances to DROP TABLE/CREATE TABLE than
it would be to TRUNCATE or DELETE. Only testing will prove which works
better for you.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Martin Gainty"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
com> cc:
Fax to:
06/25/2004 12:43 Subject: Optimising SQL Statements
PM
Hello All:
I have a very simple test procedure
PROCEDURE TEST_PROCEDURE AS
BEGIN
DELETE FROM X;
COMMIT;
INSERT INTO X (column1)
SELECT
Y.y_id
FROM
Y_TABLE Y
WHERE
Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
COMMIT;
END;
this very simple procedure takes 5 min 30 sec to complete its very basic
delete and insert operations
Any ideas on how I can optimise
(I used truncate instead of delete and that helped big time)
Vielen Danke,
-Martin
Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relating to the official
business of Laconia Data Systems (LDS) is proprietary to the company. It is
confidential, legally privileged and protected by law. LDS does not own and
endorse any other content.
(cell) 617-852-7822
(e) [EMAIL PROTECTED]
(http)www.laconiadatasystems.com
>From: "Ron McKeever" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Subject: Location of files
>Date: Fri, 25 Jun 2004 07:47:18 -0700
>MIME-Version: 1.0
>Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com
>with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700
>Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 -0000
>Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 -0000
>Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED]
>designates 207.217.120.74 as permitted sender)
>X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m
>Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
>List-ID: <mysql.mysql.com>
>Precedence: bulk
>List-Help: <mailto:[EMAIL PROTECTED]>
>List-Unsubscribe:
><mailto:[EMAIL PROTECTED]>
>List-Post: <mailto:[EMAIL PROTECTED]>
>List-Archive: http://lists.mysql.com/mysql/167906
>Delivered-To: mailing list [EMAIL PROTECTED]
>Message-ID: <[EMAIL PROTECTED]>
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
>X-Virus-Checked: Checked
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC)
>FILETIME=[C575A2C0:01C45AC3]
>
>Is there any benefit to having the .MYD files on one drive, and the .MYI
on
>its own dedicated hard drive??
>
>
>rm
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar ? get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
