Member Avatar for greatdevelopers

A DELETE statement requires an exclusive lock on a table.If there are a significant number of deletes while simultaneously there is also a lot of SELECT traffic, this may impact performance. One trick that can be used is to turn the delete into an insert!

Consider the following example:

CREATE TABLE events
(eventid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title CHAR(40));
Instead of deleting from the events table, interfering with all the selects, we do the following:

CREATE TABLE event_deletes (eventid INT UNSIGNED PRIMARY KEY);
To delete an event:

INSERT INTO event_deletes (329);

Now, to retrieve all non-deleted events:

SELECT e.eventid, e.title
FROM events e
LEFT JOIN event_deletes ed
ON e.eventid = ed.eventid
WHERE ed.eventid IS NULL;
or with a subquery :

SELECT e.eventid,e.title
FROM events e
WHERE NOT EXISTS
(SELECT * FROM event_deletes ed WHERE ed.eventid = e.eventid);
These SELECT statements merely use the index on the eventid column in the event_deletes table, no row data needs to be retrieved. During a maintenance timeslot, a script can go through the deleted items and delete the actual rows from the main table.

DELETE events
FROM events e,event_deletes ed
WHERE e.eventid = ed.eventid
DELETE FROM events_delete

  • 2 Contributors
  • 1 Reply
  • 92 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by kronass
Member Avatar for kronass

Thank you very much for the tip but i have a question
in this case I have to add an other join to the select Query and as every one knows that the joins decrease the performance in the query and they recommend to decrease the joins as much as possible if the performance in the Query is highly considered
and subquery doesn't make it look better then join in performance.
and secondly if I need to delete any thing instead of one query I have to do three queries
first insert to delete table
secondly delete from the main table
third delete from delete table
I know in database these operations are nothing and can be preformed fast.

may be this method is not for low load database requests

what I need is a prove that this tip is right, and how you got to this conclusion
if you proved it to me that this works faster with heavy load requests I will definitely use it

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.