Database

Database Index Size Estimator

Estimates what each index on a table costs on disk, and how deep its tree gets.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Paste the CREATE TABLE, including the PRIMARY KEY, UNIQUE and REFERENCES clauses — those are what the tool turns into indexes.
  2. Pick the engine and set the row count.
  3. Select Estimate the indexes.
  4. Compare the entry width column across the indexes — that single number explains most of the size difference between them.
  5. Treat the totals as an order of magnitude, not a measurement: real fragmentation and real value distribution are not knowable from DDL.

What index size estimator does

Indexes are usually discussed as though they were free, and then somebody notices that the indexes on a table are larger than the table. A b-tree leaf entry is the key plus a pointer to the row plus a few bytes of per-entry overhead, and on InnoDB and on any clustered table the pointer is the whole primary key — so a wide primary key quietly makes every secondary index on that table wider too. Above the leaves sit the internal levels, and every extra level is another page read on a cold lookup.

This page reads the primary key, unique constraints and foreign key references out of your CREATE TABLE and estimates each resulting index: the key width, the full entry width including the row locator, how many entries fit on a page at your fill factor, how many levels the tree ends up with, and the total size. It is an estimate and it is labelled as one — nothing here can know your real value distribution or how much the pages have fragmented. What it is good for is comparing: a 130-byte email index against an 8-byte integer one, before either exists.

Frequently asked questions

The ones your DDL declares: the primary key, every UNIQUE column, and every column with a REFERENCES clause — foreign keys are indexed automatically on MySQL and usually should be indexed by hand elsewhere. Indexes created in a separate CREATE INDEX statement are not read. Turn off the unique and foreign key option to see the primary key alone.

Because InnoDB secondary indexes store the primary key as the row locator rather than a physical pointer. Every leaf entry is therefore the indexed column plus the whole primary key. That is the single strongest argument for a narrow primary key, and it is why the same schema on PostgreSQL produces different numbers here.

How full each page is assumed to be. A lower fill factor leaves room for later inserts in the middle of the index and so avoids page splits, at the cost of a larger index and more pages to read. 90% is the usual default for a mostly-append workload; drop it towards 70% for an index whose keys are inserted at random.

It is an estimate and should be treated as one. It cannot know your real key distribution, how fragmented the pages have become, or whether the engine deduplicated repeated keys. Use it to compare one candidate index against another — that comparison is reliable — rather than to predict an exact figure.