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.
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:
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:
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:
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:
Would delete all rows of ID lower than 500 from the partition named part_7. Data from partitions can also be deleted like so:
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):
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!
