Saturday 26 April 2014

Delete dupicate entries in mysql table

CREATE TABLE wpdb.wp_posts_deduped like wpdb.wp_posts;
INSERT wpdb.wp_posts_deduped SELECT * FROM wpdb.wp_posts GROUP BY post_content, post_title;
RENAME TABLE wpdb.wp_posts TO wpdb.wp_posts_with_dupes;
RENAME TABLE wpdb.wp_posts_deduped TO wpdb.wp_posts;
#OPTIONAL
ALTER TABLE wpdb.wp_posts ADD UNIQUE `unique_index` (`post_content`, `post_title`);
#OPTIONAL
DROP TABLE wpdb.wp_posts_with_dupes;

Find mysql database and tabe size

These queries are very useful to find bloated databases/tables:

Show size of all databases (in MB)

SELECT s.schema_name, 
   SUM(t.data_length + t.index_length)/1024/1024 total_size, 
  (SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used, 
   SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC;



Show size of all tables (in MB)

SELECT s.schema_name, table_name,   
  SUM(t.data_length + t.index_length)/1024/1024 total_size,   
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used,  
  SUM(data_free)/1024/1024 data_free 
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t 
    ON s.schema_name = t.table_schema 
GROUP BY s.schema_name, table_name 
ORDER BY total_size DESC;


Source