Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

optimizing a sql syntax

Posted on 2011-03-06
5
Medium Priority
?
264 Views
Last Modified: 2012-05-11
I have few tables under the database: CRMDB.
The table books is very huge, almost 13712821 rows [aprox].
I feel that the following sysntax would be very costly:
delete from CRMDB.books where custId not in (select custId from CRMDB.customers);

Is there any way to overcome the costly not in command??
0
Comment
Question by:pvinodp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 800 total points
ID: 35053747
Backup database and try these alternative queries
DELETE b.*
FROM    CRMDB.books b
        LEFT JOIN CRMDB.customers c ON b.custId = c.custId
WHERE   c.custId IS NULL

Open in new window


DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT *
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId ) ;

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1200 total points
ID: 35053806
Instead of * you can go with 1 in the sub-query.
DELETE  CRMDB.books
WHERE   NOT EXISTS ( SELECT 1
                     FROM   CRMDB.customers
                     WHERE  CRMDB.books.custId = CRMDB.customers.custId );

Open in new window

0
 

Author Comment

by:pvinodp
ID: 35054112

I tried and getting this error:

delete from omcr.HistoricalAlarmNotification WHERE   NOT EXISTS ( select 1 from omcr.HistoricalAlarmLifecycle where omcr.HistoricalAlarmNotification.halcid omcr.HistoricalAlarmLifecycle.halcid );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'omcr.HistoricalAlarmLifecycle.halcid )' at line 1
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35054155
you missed =
DELETE FROM omcr.HistoricalAlarmNotification 
      WHERE NOT EXISTS (SELECT 1 
                          FROM omcr.HistoricalAlarmLifecycle 
                         WHERE omcr.HistoricalAlarmNotification.halcid = omcr.HistoricalAlarmLifecycle.halcid);

Open in new window

0
 

Author Closing Comment

by:pvinodp
ID: 35125840
I thank you all for your inputs
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question