Skip to content

Instantly share code, notes, and snippets.

@mgttt

mgttt/mysql table size etc

Last active October 30, 2020 11:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mgttt/5b5fe34923808cc70bc09d55cc0ce86c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mgttt/5b5fe34923808cc70bc09d55cc0ce86c to your computer and use it in GitHub Desktop.
Download ZIP
mysql table size etc
Raw
mysql table size etc
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES
where DATA_LENGTH+INDEX_LENGTH >0 and TABLE_SCHEMA not in ('mysql','sys','information_schema');
select * from information_schema.TABLES limit 1;
@mgttt
Copy link
Author

mgttt commented Oct 19, 2020 •
edited
Loading

select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) TBL, COLUMN_NAME as col, DATA_TYPE as type from information_schema.columns where TABLE_SCHEMA not in ('mysql','sys','information_schema','performance_schema') limit 100;

select count(*) from information_schema.columns where TABLE_SCHEMA not in ('mysql','sys','information_schema','performance_schema')

@mgttt
Copy link
Author

mgttt commented Oct 19, 2020 •
edited
Loading 

TODO: GROUP BY COL 
=> 
if count(*)>ct_manually then:
    manually tick needed; or sampling
else:
    count (<max_count) or manually tick => do group table :
call build_col_rpt(col)

@mgttt
Copy link
Author

mgttt commented Oct 30, 2020

SELECT table_schema, table_name, engine, table_rows,update_time,avg_row_length FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
ORDER BY table_rows * avg_row_length DESC;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment