Z-Car

MySQL – Best way to remove all data from a table

girl holding stop sign

There are multiple ways in which to remove all data from a MySQL Table.

The first is to use the DROP TABLE command, which will remove the entire table with data.  This basically removes the entire table, you will not be able to recover the structure or data.

The second is DELETE * FROM Table which will remove the data,  leaving the auto-increment values alone, as well as the rest of the table structure.  This method is very slow on large tables using Innodb.  This is because each record has to be written to the log.  In general, you should never use this method to remove all data from a table.

The third method is to issue a TRUNCATE TABLE command which will quickly remove all the data from the table, reset the auto-increment values (but leave them as auto-increment columns, so they will just start at 1 and go up from there again).

Lastly, you can use the nuclear option, which is as follows :

mysqldump -ppassword YourDBName YourTblName –no-data dumpfile | mysql dumpfile

This will drop the table, and then recreate it all from scratch.


MySQL – Constraints on Hierarchical Data in a Self-Referential Table

blonde-woman-working-on-her-laptop-from-home-5

I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.

This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.


MySQL Master and Slave have different Query Execution Plans

bikini girl using computer

I recently ran into a situation where a Delete across a large time period was taking an extensive amount of time to run when replicated to the Slave, although the query ran very quickly when running on the Master. On the Master, the query took

After much head scratching and verifying that indexes existed on both Master and Slave, we took a few minutes to compare the query execution plan using EXPLAIN. It was quickly obvious that the Slave was using a different index than the Master when executing the query. We quickly update the table statistics using Analyze Table, and re-ran EXPLAIN. After the Analyze Table, the problem was solved, and after restarting replication using Start Slave, the offending query quickly executed in 2 minutes.

Moral of the Story: Running Analyze Table on larger tables every couple days is probably a good idea.


MySQL How to delete duplicate records and rows of data

15647301_s

The fastest and easiest way to delete duplicate records is my issuing a very simple command.

alter ignore table [tablename] add unique index `unique_index` ([fieldname])

What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.

This also will work, but is not as elegant:

delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )