CRUD
DELETE

DELETE Queries – Advanced CRUD explanation part 4

intro

DELETE queries are a necessity whenever we find ourselves deleting data within a database. Join us for a thorough walkthrough of what they are, how they work, and when should they be used.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

DELETE queries are queries that are being run whenever data inside of a table is being deleted. Seriously, delete anything – a DELETE query will take place. That shouldn’t come as a surprise since deleting data is an inevitable part of the life of any DBA or even a developer. After all, after mistakes are made, they need to be erased, right?

DELETE queries help us delete data within our database – they’re frequently used straight after inserting something wrong or also when doing regular maintenance, cleaning up tables, or preparing them for normalization.

DELETE Explained

In most cases, DELETE queries look like so:

A Basic Form of a DELETE Query.
A Basic Form of a DELETE Query.

Well, to tell you the truth, DELETE queries don’t always look exactly like that – we’ve added the QUICK statement in between just to see if you’ve been attentive. As with all queries within the SQL ecosystem, the DELETE query also has a couple of parameters unique to itself that can be specified. Some of them are as follows:

  • LOW_PRIORITY – such a statement will make the DELETE query lower in priority compared to other queries (useful when running bulk queries.)
  • QUICK – such a statement will tell MySQL how to act on indexes when deleting data. If this keyword is specified, if the data is deleted and table will have “blank spaces” inside of it, new data would be inserted into those blank spaces. This statement is only applicable to the now-obsolete MyISAM storage engine within MySQL though.
  • PARTITION – the PARTITION keyword will let a user delete specific rows from a specific partition.
  • IGNORE – the IGNORE keyword will ignore all errors encountered by MySQL and continue running the SQL query in question.
  • WHERE – the WHERE keyword will let the user specify a clause specifying which rows to delete (only rows that match the condition will be affected.)
  • ORDER BY – this keyword can be used if we want to delete data in exactly the same order that we specify them.
  • LIMIT – very useful if we want to delete only a specific amount of rows (for example, only the first 50 or 100 rows.)

How to Work With DELETE?

DELETE queries should be used whenever we need to delete data from a table. That’s not it, though: they’re slowed down by indexes or partitions since when indexes or partitions are in use, our database needs to update (delete) data from those indexes or partitions as well. Here’s an explanation of the query:

An Explanation of the DELETE Query.
An Explanation of the DELETE Query.

As you can see, the DBMS still evaluates the possible indexes to use and uses the index on “email” – 1 rows are deleted, and no partitions are in place.

There’s not that much to do to optimize a DELETE query, really – dropping all (or the majority of) indexes and partitions that exist on a table will help (the more data we have, the more visible the impact will be.)

There are a couple more things we should know, though: many DBAs use DELETE queries to delete data belonging to specific partitions, and that’s because DELETE supports partition selection. When deleting select data in partitions, many DBAs elect to use the less or more signs, and that’s because many tables have automatically incrementing ID columns. That means that a query like so:

DELETE and Partitions.
DELETE and Partitions.

Would delete all rows of ID lower than 500 from the partition named part_7. Data from partitions can also be deleted like so:

Truncating a Partition with DELETE.
Truncating a Partition with DELETE.

For many DBAs, the second query (example above) will be more preferable than the first one since the second query will have the same effect as a TRUNCATE query (covered below.)

There’s not that much that can be said about DELETE queries, however, some developers may not be aware of the fact that DELETE queries can be swapped with TRUNCATE queries in specific cases. There’s one caveat – TRUNCATE queries delete all rows within a specific table, while a DELETE clause would let us specify a specific clause with a WHERE.

DELETEs Long Lost Brother – TRUNCATE (And Other Life Hacks)

You want to know how to overcome 70% of the problems caused by DELETE queries? Switch the DELETE query to a TRUNCATE and instead of executing queries like DELETE FROM demo_table PARTITION (part_7); (example above) execute something like TRUNCATE demo_table PARTITION (part_7);

TRUNCATE query will always be significantly faster than a DELETE because of one key reason – there’s very little overhead and very few things to consider for our database when executing TRUNCATE queries when compared with DELETE queries.

DELETE queries can also be sped up like so (assume we have two tables – demo – and demo_2. demo is the table we have data that we need to move to demo_2 and then delete. The table demo_2 is of exactly the same structure as the table demo):

  1. Run an INSERT INTO query combined with a SELECT query – craft the SELECT query in such a way that it only selects rows that you want to keep.
  2. Use a RENAME TABLE query to “mark” the demo table to be deleted, and switch the demo_2 table to the demo table (original table.)
  3. Drop the old table.
Deleting data with INSERT & RENAME.
Deleting data with INSERT & RENAME.

Such operations will most likely be faster because INSERT INTO SELECT... queries have little overhead and both RENAME TABLE and DROP TABLE queries are blazing fast too.

Also keep in mind than when MyISAM is in use, MySQL will maintain a list of deleted rows in order to let INSERT queries re-use their positions.

That’s all there really is to it! Now, grab a free trial of the most highly rated SQL client – DbVisualizer – and watch your database performance, availability, and security skyrocket. Also follow us on Twitter, and continue keeping an eye on our blog to stay updated on all of the latest developments. Until next time!

Summary

In the final part of the CRUD series, one of our experts has walked you through DELETE queries within database management systems – DELETE queries are one of the most frequently used type of query when developers or DBAs elect to delete data from their database instances, but a TRUNCATE query (a query that deletes all existing rows within a table) can also be used.

We have shared a couple of tricks to make your life as a DBA easier, but by far the trick that’s helped the most companies (and we’re talking giants – Tesla, Meta, and Netflix included) is the usage of a proper SQL client built by database experts – DbVisualizer. Take proper care of your database instances – follow the advice given in these series, grab a free trial of DbVisualizer today, and until next time!

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

The SQL DELETE Statement Explained

author Leslie S. Gyamfi tags DELETE SQL 4 min 2024-06-03
title

Deleting a Column in SQL: Everything You Need to Know

author Antonello Zanini tags DELETE MySQL ORACLE POSTGRESQL SQL SQL SERVER 9 min 2023-09-19
title

UPDATE Queries - Advanced CRUD explanation part 3

author Lukas Vileikis tags CRUD UPDATE 7 MINS 2023-06-08
title

SELECT Queries - Advanced CRUD explanation part 2

author Lukas Vileikis tags CRUD SELECT 6 MINS 2023-06-07
title

INSERT Queries - Advanced CRUD explanation part 1

author Lukas Vileikis tags CRUD INSERT 4 MINS 2023-06-07
title

SQL Transactions in a Flask CRUD Application

author Ochuko Onojakpor tags CRUD SQL TRANSACTIONS 8 MINS 2022-12-16
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗