1

I am little confused. I have to estimate the size of the table to fit 2 million rows. I have no idea how much space primary and secondary indexes takes. Especially with composite primary and secondary indexes. Structure of the table is something like

Database Engine: innodb 

create table abc(

a  int,
b  varchar(30),
c  char(10),
d  bigint(8),

FOREIGN KEY(a) 
        REFERENCES af(a_id) 
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,
primary key(a,b,c)
)

CREATE UNIQUE INDEX idx_abc
  ON abc
    ( a ASC, d  ASC);

CREATE INDEX idx_abc2
  ON abc
    ( d );

Please help

Sonu

FallAndLearn
  • 4,035
  • 1
  • 18
  • 24
Sunil Sharma
  • 21
  • 8
  • With only 2 million rows and each row only about 50 bytes, this is not worth worrying about. more so if you drop idx_abc2 and change idx_abc to (d ASC, a ASC) – e4c5 Dec 19 '16 at 10:36
  • that is just an estimation to understand the sizing part. data will be very big. – Sunil Sharma Dec 19 '16 at 10:45

2 Answers2

0

You can get the size for data/indexes using mysql.innodb_index_stats. Warning, the size is given in pages units => You must multiply it by the pagesize which is usually of 16K.

aCOSwt
  • 111
  • 4
0

To do an exact estimation you need to create a copy of the table and generate 2 000 000 volatile data into it, representing the actual size. Measure the table along with indexes as shown in other answers and then, knowing the answer, you can remove the copy.

If preciseness is not as important in your case, then multiply the number of bytes a record will occupy with the number of records and do the same with indexes.

record * 2 000 000 + index * 2 000 000 ~= 50 * 2 000 000 + 60 * 2 000 000 = 110 * 2 000 000 = 22 000 000

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • I don't mind if the space is on little bit higher side. i come to know that for primary index does not take any space. is that true even if it is a composite primary key? And what is the way to calculate the secondary index size? – Sunil Sharma Dec 19 '16 at 11:11
  • @Sunil, actually as the table increases, the size of the primary key increases as well. When there is no primary key, InnoDB creates a hidden clustered index, this is why it seems the index does not add to size, but as a matter of fact it does, however, the fact that a hidden index compensates for it, adding some size to the table as well is misleading. Read more here: http://dba.stackexchange.com/questions/44520/does-the-size-of-the-primary-key-contribute-to-table-size – Lajos Arpad Dec 19 '16 at 11:48