intro
All DBAs deal with questionable and weird operations at some point in their careers – some of them bigger than others and that’s a fact. What’s also a fact is that DBAs frequently need to purge (delete) data in their database instances: doing that for 1000 rows may not be a challenge, but for 200 million? Hmm..
In this blog, we’re walking you through how best to perform bigger SQL DELETE operations against your database instances. Want to join?
DELETE SQL Queries Explained
It’s not rocket science and it’s not news to anyone – DELETE FROM SQL queries are queries that are used to delete data from our database instances. In most cases, everything looks like so:
Does it need explaining? Nope, it’s nothing impressive. There are a couple of things you need to know though:
Optimizing DELETEs
DELETE queries delete a row from sql table and are usually very easy to optimize – database management systems themselves (we’re talking about MySQL server here) used to say that DELETE SQL queries are usually optimized just like INSERTs. In other words, to optimize the deletion of data, we look at DELETE queries the same way as we would look into INSERT queries. That means that:
SQL DELETE queries also have a couple of caveats unique to themselves – TRUNCATE queries will always be faster than DELETEs if you need to delete all of the data that exists in a given table, and they can also be used together with subqueries (TRUNCATE queries cannot.)
Here’s how a sample DELETE query together with a subquery would look like:
This query would delete data that matches a NOT EXISTS clause and has an ID lower than 25,000. Such SQL queries are the norm in many content management systems as well as more complex data projects.
Some DBAs also employ lifehacks to delete data – some just export the data, run a SQL delete statement to delete the column in question when the table is being created in a SQL file, and then switch INSERT to INSERT IGNORE statements to ignore upcoming errors posed by the DBMS.
The same can be said about UPDATEs – to avoid running tedious UPDATE queries when you’re updating the entire table, export your data except from the column you’re updating, set the column you want to update to have a default value, then re-import your data. Your data will be pre-filled without any additional work done by the DBMS. Sometimes such tricks can save you multiple hours of your precious time.
If deletion of all data within a table is the goal of your SQL DELETE statement, drop DELETE and use TRUNCATE instead – it’s blazing fast because it’s made for that exact purpose. Its syntax looks like the following and it doesn’t have any additional clauses or hidden things in between:
1
TRUNCATE [your_table];
SQL Clients
Of course, as any optimization goes, we’d be ashamed not to mention SQL clients. Good SQL clients will tell you how long your SQL queries take, help you optimize your databases and visualize SQL queries in a nice manner, help you edit your data like a spreadsheet through a nice CLI interface, and even complete your SQL queries for you.
Oh, did we mention that you have the ability to set a master password to protect your data so that vital data doesn’t ever leave your computer? Now we did!
DbVisualizer also offers a free 30-day trial run of the software to everyone who clicks here, so if you didn’t do so yet, start evaluating the tool today and tell us your results the next time you come to the blog!
