CODEWITHSUNDEEP
mysqldatabasedatabase-designinnodbdatabase-schema
Ahsan Zaheer
Ahsan Zaheer

Reputation: 676

How can I see how InnoDB stores my data, based on DB structure?

Is there any tool or command which shows how InnoDB stores my database, based on DB structure. I want to see how InnoDB stores my table data. I want to change table structure to improve delete query.

I want to see how the structure effects the performance of delete query.

Upvotes: 0

Views: 70

Answers (1)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

To check how it stores the data, you have to look into the folders of MySQL. Depending on operating system and loca setting it stores it per table or in just one big file.

To check the current status, execute

SHOW STATUS

You can also apply a WHERE to this like this

SHOW STATUS WHERE variable_name like '%pages%'

This will in details show any status with 'pages' in the variable name.

To check how big your system is i use this command, this breaks it into per table level.

SELECT 
table_name AS `Table`,
concat(round(sum(table_rows)/1000000,2),'M') '# of rows',
concat(round(sum(data_length)/(1024*1024*1024),2),'G') 'Size of data',
concat(round(sum(index_length)/(1024*1024*1024),2),'G') 'Size of index',
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') 'Total size',
round(sum(index_length)/sum(data_length),2) 'Index fraction'
FROM information_schema.TABLES
GROUP BY table_name;

Is this what you are looking for?

Upvotes: 3

Related Questions