MySQL: Use TRUNCATE to Reuse Deleted Indexes
This may also be rephrased as “Efficiently Remove All But Top n Rows from a MySQL Table”
This technique is useful if, for example, “x” errant rows were recently added to a table and you not only want to programmatically delete those rows, but also reuse their auto increment values:
CREATE TEMPORARY TABLE tbl_ad_hoc AS (SELECT * FROM tbl_gold_prices ORDER BY barID ASC #Where: 'barID' is our auto increment primary key LIMIT n ); TRUNCATE TABLE tbl_gold_prices; INSERT INTO tbl_gold_prices SELECT * FROM tbl_ad_hoc;
Specific Example and Solution Explanation
We have a 40 row table and recently added 10 rows that were in error. How can we programmatically (i.e., by code and not manually) remove these 10 new rows but also preserve a clean sequence of auto increment values for our index and primary key?
- Create a temporary table. This will be used to store our “good” rows
- Sort our main table by primary key from lowest value to highest (i.e., in ascending order)
LIMITto select only the first 30 rows (remember, the last 10 rows were errant and we don’t want these). The
ASCcommand in step 2 will ensure that the latest 10 rows are at the bottom of the sort
- Remove all rows from our main table using truncate command. All table and column properties will be retained. Only the rows will be deleted
- Finally, copy the “good” rows into our truncated main table
Caveats and Items to Consider
NOTE: Replace the “n” in the above SQL with the number of rows you want to keep. For example, to keep the top 30 rows and delete everything else, use
TEMPORARY table is visible only to the current connection. It is dropped automatically when the connection is closed.
TRUNCATEdoes not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.
- Stack Overflow: How LIMIT Works
- MySQL Temporary Tables
- Stack Overflow: Efficiently Remove All But Top n Rows
- MySQl Truncate Table Man docs