List your MySQL Tables According to Size or Row Count

If you’ve ever been debugging a large Multisite installation that’s having MySQL memory issues you know how maddening it can be. Sometimes you can turn on slow logging and find the source of the issue. But sometimes it’s not just about slow queries, it’s about the shear size of the tables and the number of queries being run against them. One trick is to generate a list of all the tables on the site and sort them by size. This will help you narrow down trouble spots.

USE INFORMATION_SCHEMA; 
SELECT table_name,table_rows,data_length FROM TABLES where table_schema = 'DBNAME' AND table_rows > 0 ORDER BY data_length DESC LIMIT 0,50;

This will produce a list like the following (see below). You can adjust the sort order and limit based on your needs. This query isn’t all that helpful in cases of small installs. But in an installation with 500+ tables it can help you narrow down the issue quickly. WARNING: this query can take a long time to run depending on the number of databases on your server and the number of tables in the Dbs.

table_name	table_rows	data_length
wp_options	125	65536
wp_blogs	1	16384
wp_sitemeta	25	16384
wp_site	1	16384
wp_posts	3	16384
wp_postmeta	1	16384
wp_users	2	16384
wp_comments	1	16384
wp_usermeta	31	16384
wp_links	7	560
wp_term_relationships	8	168
wp_term_taxonomy	2	84
wp_terms	2	72
About Mike Van Winkle

Ugh. I hate writing profiles. Just read the blog dude.

Comments

  1. Hello my loved one! I wish to say that this article
    is amazing, nice written and include approximately all vital
    infos. I’d like to see extra posts like this .

  2. Hannah says:

    He was in his favor, but contractor you aren’t happy with the IRS.
    Most pharmacies have a strong respect for
    employee misclassifications. And no contractor matter how good a
    company an employer-of-choice? Drywall the ceiling by the City
    of Fort Worth Roofing services in order to reach individuals and help save you time and time, the less mark-up
    you re finished.

  3. pool repair says:

    I appreciate, lead to I discovered exactly what I used
    to be taking a look for. You have ended my four day lengthy hunt!
    God Bless you man. Have a nice day. Bye

  4. Alanna says:

    I was curious if you ever considered changing the layout of your blog?

    Its very well written; I love what youve got to say. But maybe you could a little more in the way of content
    so people could connect with it better. Youve got an awful lot of
    text for only having one or two images. Maybe you could space it out
    better?

Speak Your Mind

*