0
  1. At T1, insert comes for table_1, goes to disk block_1.
  2. At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ?
  3. At T3, again insert comes for table_1, can it go either block_1 or block_2 based on available space ?
  • If by "disk block" you mean the database file page, a page can belong to one table only; you can't have records from different tables in the same page. – mustaccio May 3 at 15:52
0

InnoDB (the default Engine in MySQL) allocates separate "blocks" for each table.

A "block" is 16KB, regardless of what size the OS or disk controller does.

Yes, a table with one small row is "wasting" a lot of disk space. But if you have a million rows, the percentage waste will be lower.

Similarly, each INDEX (except for leaf nodes of the PRIMARY KEY) will use disk in blocks of 16KB.

Use this to see the "Data_length" and "Index_length":

SHOW TABLE STATUS LIKE 'T1';

I created a table with 2 secondary indexes, but have not put any rows in it yet:

mysql> SHOW TABLE STATUS LIKE 'mod%'\G
*************************** 1. row ***************************
           Name: modification
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384        -- one 16KB block for data
Max_data_length: 0
   Index_length: 32768        -- two 16KB blocks, one per index
      Data_free: 0
  ...

Those 3 blocks belong to that one table; no sharing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.