Useful MySQL Queries For WordPress Migration

CodeSleepShred

See what’s hogging up your MySQL db


# query db to see the largest tables
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

view raw

gistfile1.txt

hosted with ❤ by GitHub

Revisions Genocide


# revisions genocide
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

view raw

gistfile1.txt

hosted with ❤ by GitHub

Cluttered up DB, create a mass drop query


SELECT CONCAT( 'DROP TABLE `', TABLE_NAME, '`;' ) AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%_gmp_%'

view raw

gistfile1.txt

hosted with ❤ by GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks: 

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax