MySQL Index Size

Question

I have two tables with exactly same primary keys. For example, the school_name columns in table1 and table2 have the same values (those two tables have the same number of rows).

Table 1:

CREATE TABLE `table1` (
    `school_name` varchar(512) NOT NULL,
    `descp` mediumtext,
    PRIMARY KEY (`school_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Table 2:

CREATE TABLE `table2` (
    `school_name` varchar(512) NOT NULL,
    `address` mediumtext,
    --5 more fields here...
    PRIMARY KEY (`school_name`),
    --4 more other index...
    --E.G., KEY field_3_index (field_3)...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Here is the index size result:

+---------------+-----------------+--------------------+------------+
| database_name | table_name      | index_name         | size_in_mb |
+---------------+-----------------+--------------------+------------+
| schoolsDB     | table1          | PRIMARY            |   10355.97 |
| schoolsDB     | table2          | PRIMARY            |     794.69 |
+---------------+-----------------+--------------------+------------+

Why does table2's PRIMARY KEY index much larger than table1's?

Looks like it is due to those extra columns and index in table2, but I don't understand the reason behind it.

Thanks for help!

For InnoDB, the primary key basically is the complete table (e.g. InnoDB stores all the data in an index). See e.g. [Does MySQL create an extra index for primary key or uses the data itself as an “index”](https://stackoverflow.com/q/57134422). — Solarflare, Dec 24 '20 at 01:38
@Solarflare Thanks! How about myisam? Should the size of index be same if it is using myisam? — Top.Deck, Dec 24 '20 at 02:42
Yes, for MyISAM, they would have similar sizes. But is that relevant? The table data is still stored and requires space. So MyISAM has: Primary key (small), additional structure with row data (actually repeating the primary key column, since that is data too). InnoDB has: Primary key (incl. all data), NO additional stucture with row data. So the smaller primary key size itself does not make your storage requirements lower, if that is what you are after. — Solarflare, Dec 24 '20 at 03:05
@Solarflare thanks for the detailed explanation! Do you mind post the answer so I could accept it? — Top.Deck, Dec 24 '20 at 03:10
Tip: [Don't use MyISAM](https://stackoverflow.com/a/17706717/20860). — Bill Karwin, Dec 24 '20 at 05:43
*Here is the index size result* Have you executed OPTIMIZE TABLE before gathering this statistic? — Akina, Dec 24 '20 at 05:46
@Akina No, I didn't. Just Googled ` OPTIMIZE TABLE` and I realized I should. Unfortunately I couldn't. Because `OPTIMIZE TABLE` will lock the table for a while, which is under PROD environment. — Top.Deck, Dec 24 '20 at 05:52
Without OPTIMIZE TABLE (which compresses unused space in the table file) your statistic cannot be used for to draw some conclusions. — Akina, Dec 24 '20 at 05:57
Please provide `SHOW TABLE STATUS`; it has more info and may help to clarify things. — Rick James, Dec 24 '20 at 17:04

score 1 · Accepted Answer · answered Dec 24 '20 at 17:03

Don't use MyISAM. Switch to InnoDB.
InnoDB disk footprint with be 2x-3x that of MyISAM. (This includes Data, Index, and "free" space.)
Don't use OPTIMIZE TABLE on InnoDB; it is a waste of time. (There are rare exceptions.) Yes, OPTIMIZE removes some of the fragmented space, but it quickly gets chewed up again as you insert/update/delete.
MyISAM's indexes each look alike -- the key plus a pointer.
InnoDB's PRIMARY KEY is really the data, sorted by the PK. Hence its size is just the extra (non-leaf) nodes in the BTree structure.
InnoDB's secondary keys include the key's column(s), plus the PK's column(s).
In most cases, it is poor schema design to have a 1:1 pairing of tables. Your case may be a good example of an exception -- moving a bulky column (description) that is rarely used out of the main table (table2).
You may find that InnoDB's "compressed" is not very useful. It saves some disk space, but does not necessarily help with speed.

MySQL Index Size

1 Answers1