Two usefull MySQL requests

To analyse a database content, I used several times theses two requests:

  • Number of columns for all tables in a database:
    select TABLE_NAME, count(TABLE_NAME) from columns where TABLE_SCHEMA="dbname" group by TABLE_NAME order by count (TABLE_NAME);
  • Number of rows for all tables in a database:
    select table_name, table_rows from information_schema.tables where table_schema = 'dbname' order by table_rows;
  • These are simple but effective to know where most of your data are if your database starting to get slow.
    Also, it can help for finding a partitionning or sharding strategy.

    Advertisements

1 Comment »

  1. Kerstin said

    Just dropping by.Btw, you website have great content!

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: