Hi Martin, Speaking more from a general standpoint (I mostly work with MS SQL, but my home projects are MySQL - these days there is precious little time for those, though)..
Assuming you have appropriate indexes on tables y and y2: 1. Truncate WILL be faster than delete, as it is a non-logged operation. Deleting is comparatively slow. If the goal is to remove 'old' records no longer in the Y set from X, it may be better to just delete those rows no longer in Y (see below), rather than the whole lot. 2. It may be better to do a left join to tabley (on y_id, and y_id IS NULL), although I do not know quite how mysql handles these things - for MS, where not exists / left join where null is quicker than not in. 3. If X is indexed, it may be faster to drop those indexes, bulk-insert data, and recreate them. This is often better when dealing with large sets of data, since it is SO much quicker to insert into an un-indexed table and then create an index, compared with inserting into the table and updating the index for each row. Hope this helps! Matt > -----Original Message----- > From: Martin Gainty [mailto:[EMAIL PROTECTED] > Sent: 25 June 2004 17:43 > To: [EMAIL PROTECTED] > Subject: Optimising SQL Statements > > 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]
