DELETE Queries – Advanced CRUD explanation part 4
Published: 2023-09-11 07:00:00

**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 this tutorial
[DbVisualizer](https://www.dbvis.com/download/), 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:
<br />
![A Basic Form of a DELETE Query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g9dkxm1wmw55ba286aat.png)
<figure><figcaption>A Basic Form of a DELETE Query.</figcaption></figure>
<br />
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:
<br />
![An Explanation of the DELETE Query.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zw4csz6g668tq7nnj5xy.png)
<figure><figcaption>An Explanation of the DELETE Query.
</figcaption></figure>
<br />
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:
<br />
![DELETE and Partitions.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0gtm7elxv46skpbzx0h7.png)
<figure><figcaption>DELETE and Partitions.</figcaption></figure>
<br />
Would delete all rows of ID lower than 500 from the partition named `part_7`. Data from partitions can also be deleted like so:
<br />
![Truncating a Partition with DELETE.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/est9hpw6g91ttk4xuq4t.png)
<figure><figcaption>Truncating a Partition with DELETE.
</figcaption></figure>
<br />
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);`

A `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.
<br />
![Deleting data with INSERT & RENAME](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cq1yiqaumj6j0zian4bd.png)
<figure><figcaption>Deleting data with INSERT & RENAME</figcaption></figure>
<br />
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](https://www.dbvis.com/eval/) – and watch your database performance, availability, and security skyrocket. [Also follow us on Twitter](https://twitter.com/dbvisualizer), and continue [keeping an eye on our blog to stay updated on all of the latest developments](https://www.dbvis.com/thetable/). 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](https://www.dbvis.com/customers/)) is the usage of a proper SQL client built by database experts – [DbVisualizer](https://www.dbvis.com/). Take proper care of your database instances – follow the advice given in these series, [grab a free trial of DbVisualizer today](https://www.dbvis.com/eval/), and until next time!


## About the author
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](https://breachdirectory.com/), frequently speaks at conferences and blogs in multiple places including his blog over at [lukasvileikis.com](https://www.lukasvileikis.com/).