DELETE Queries – Advanced CRUD explanation part 4

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:

• 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:

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):

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.

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!