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 )
We have been using Percona 5.1.43 for sometime now and have found it to be a great MySQL Build. It includes many high performance patches, and full support for XtraDB which is a tuned InnoDB implementation.
To install painlessly, create a file called Percona.repo in your /etc/yum.repos.d directory. Add the following to the file:
[percona]
name=CentOS-$releasever – Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
gpgcheck=0
Then install using Yum or Apt.
This release includes:
Performance improvements
- Improved buffer_pool scalability
- Fast recovery
- Improved IO path
- Improved rollback segment scalability
- Separate purge thread
- Limited size of data dictionary in memory
- Increased number of concurrent write transactions (undo slots) ( up to 4000 )
- Fast checksums ( in release process )
- Support of different pagesizes ( 4K, 8K, 16K) ( in release process )
Usability / operations
- Show content of buffer_pool
- Import / export of dedicated tables
- Import / export of buffer_pool
- Transactional replication
- Show internal InnoDB data dictionary
- Show InnoDB locking/io profiling in slow.log
Here’s how to pass the arguments to MySQL to restore from a Dump:
mysql [database name] < [backup file name]
The “[database name]” specifies the name of the database to restore, and “[backup file name]” specifies the full path and filename to the file generated by mysqldump, such as backup.sql. You should also specify your MySQL username and password as usual using -uroot -ppassword.
If I wanted to restore the database named maindatabase from the file named backup.sql, I would run:
mysql maindatabase< backup.sql
1) Optimize your WHERE clause.
- Remove unnecessary parentheses, this will speed up evaluation
- COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables, but NOT Innodb tables. Beware.
- If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table
I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables. I can never understand why developers will create foreign keys, however then not write their code properly to prevent errors, or at least attempt to catch those errors.
The code below will quickly create a script that removes all Foreign Keys from a database. Many folks will simply change the engine type to MyIsam and then back to InnoDB. This may work, but it is very slow if you already have a lot of data in your tables.
select concat(‘alter table ‘,table_schema,’.',table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;') from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;
You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’
Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.