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_nametable_rowsdata_length
wp_options12565536
wp_blogs116384
wp_sitemeta2516384
wp_site116384
wp_posts316384
wp_postmeta116384
wp_users216384
wp_comments116384
wp_usermeta3116384
wp_links7560
wp_term_relationships8168
wp_term_taxonomy284
wp_terms272

13 thoughts on “List your MySQL Tables According to Size or Row Count

  1. 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.

  2. 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?

  3. I was more than happy to seek out this web-site.I wished to thanks in your time for this glorious read!! I positively enjoying each little little bit of it and I have you bookmarked to check out new stuff you blog post.

  4. It is perfect time to make a few plans for the longer term and it is time to be happy. I’ve read this publish and if I may just I want to counsel you some interesting things or advice. Maybe you could write subsequent articles regarding this article. I want to learn more issues about it!

  5. Most of whatever you say happens to be astonishingly precise and it makes me wonder the reason why I hadn’t looked at this with this light before. Your article really did turn the light on for me personally as far as this particular subject matter goes. However there is actually 1 issue I am not necessarily too comfy with and whilst I make an effort to reconcile that with the actual main theme of your issue, let me observe just what the rest of your subscribers have to point out.Nicely done.

Leave a Reply

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