DELETE
SQL

Massive SQL DELETE Operations and Database Purging Explained

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..

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

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:

https://lh7-us.googleusercontent.com/FxuKixo4hZnfYNoKHWq2MAgzH8uGQzWh6dYLTlgE2Qu16i1sp_ESucGiMqj3qMcD2ow7yUVRi75yo8PPhhw1qpdcSHeSMpNgBYNE0fHE1RzlKdh86MzJ9YXpGJceuzbkEAQqwgge3FHEuTqDGkkTN9g
Delete SQL query

Does it need explaining? Nope, it’s nothing impressive. There are a couple of things you need to know though:

  1. The DELETE cause is only used with the WHERE clause. You cannot use the clause without specifying what specifically you want to delete.
  2. DELETE is obstructed by indexes and partitions.
  3. Deleting data works differently in different scenarios (e.g. if we’re deleting data from the table itself or partitions, etc.)

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:

  • Indexes and partitions hinder DELETE queries.
  • When deleting data, using a LIMIT clause should help.

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:

DELETE with a subquery
DELETE with a subquery

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:

Copy
        
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.

Auto-completion of SQL Queries by DbVisualizer
Auto-completion of SQL Queries by DbVisualizer

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!

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

How and Why to Add an Index in SQL

author Lukas Vileikis tags MySQL POSTGRESQL Search SQL SQL SERVER 8 min 2024-11-28
title

MySQL 9.0 and the New EXPLAIN Features

author Lukas Vileikis tags MySQL SQL 9 min 2024-11-27
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2024-11-21
title

List of SQL Reserved Words in All Major Databases

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 16 min 2024-11-20
title

A Beginner’s Guide to SQL & Relational Database Management Systems

author Leslie S. Gyamfi tags BEGINNER SQL 5 min 2024-11-19
title

SQL Variable: What It Is and How To Use It

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-11-18
title

Introducing MariaDB Catalogs: What They Are and How They Work

author Lukas Vileikis tags MIGRATION MySQL SECURITY SQL 5 min 2024-11-13
title

Working with SQL Transactions

author Lukas Vileikis tags SQL Transaction 6 min 2024-11-12
title

SQL GROUP BY Clause: What It Is, How It Works, How to Approach It

author Lukas Vileikis tags DbVisualizer MySQL SQL 6 min 2024-11-06
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29

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 ↗