CODEWITHSUNDEEP
mysqlindexing
Top.Deck
Top.Deck

Reputation: 1097

MySQL Index Size

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!

Upvotes: 1

Views: 462

Answers (1)

Rick James
Rick James

Reputation: 142560

  • 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.

Upvotes: 1

Related Questions