CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-performancedatabase-administration
Anderson Tess
Anderson Tess

Reputation: 121

Performance Impact to Adding MySQL Index to Large Table

We would like to add a normal index to field 3 & 4 of the following MySQL table and would like to understand the impact to the server performance before doing so. E.g. will the index take up additional RAM and slow down the database as a result?

we understand it will take time initially to create the index. we're not concerned about that. rather, we want to know if we need to upgrade our server to anticipate for the potential increase in loading/memory pressure to the database after adding the index. our dba insists that we must increase RAM from 16GB to 48GB as he believes the new index will be kept in the RAM causing the server to run out of memory for other operations. would be great to confirm if that's necessary.

Thanks in advance for your expert advice.

MySQL version: 5.5.30

OS: CentOS

Hardware config: 8 Core, 32G RAM, 1TB Disk

Table size: 490GB

No. of rows: 67M

CREATE TABLE `mytable` (
  `field_1` text NOT NULL,
  `field_2` varchar(200) NOT NULL,
  `field_3` varchar(100) NOT NULL,
  `field_4` text NOT NULL,
  `field_5` char(8) NOT NULL,
  `field_6` varchar(100) NOT NULL DEFAULT '',
  `field_7` varchar(100) DEFAULT '',
  `field_8` varchar(20) NOT NULL,
  `field_9` char(16) NOT NULL,
  `field_0` varchar(25) NOT NULL,
  `field_a` varchar(50) NOT NULL DEFAULT '',
  `field_b` varchar(20) DEFAULT '',
  `field_c` varchar(35) DEFAULT '',
  `field_d` varchar(35) DEFAULT '',
  `field_e` varchar(30) NOT NULL DEFAULT '',
  `field_f` varchar(30) DEFAULT '',
  `field_g` varchar(3) NOT NULL DEFAULT 'xx',
  `field_h` varchar(50) DEFAULT '',
  `field_i` varchar(100) DEFAULT '',
  `field_j` char(8) NOT NULL,
  `field_k` varchar(10) NOT NULL DEFAULT '',
  `field_l` datetime NOT NULL,
  PRIMARY KEY (`field_9`),
  KEY `field_j_idx` (`field_j`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 2

Views: 3399

Answers (2)

Rick James
Rick James

Reputation: 142278

It depends. What version of MySQL do you have? With newer versions, ALGORITHM=INPLACE makes adding a secondary, non-unique, index relatively fast and painless.

You have another potential problem looming. If this table is really half the size of disk, if you do need to do an ALTER that cannot be done with INPLACE, it will probably crash for lack of disk space. Consider getting a bigger disk before this happens, and/or think about ways to shrink the table.

CHAR(8) -- what kind of data is in it? If it is always hex or plain letters, it should be declared CHARACTER SET ascii (or latin1), not utf8 -- which takes 24 bytes. Field_j already takes double that because of the index.

If some of the columns have repeated values, consider "normalizing" them. Then replace the bulky string with MEDIUMINT UNSIGNED (3 bytes, 16M max) or INT UNSIGNED.

(I understand your need for obfuscation of the column names, but it makes it hard to give you concrete suggestions.)

field_4 is TEXT, which cannot be indexed. Please describe further what type of text is in it; we may be able to suggest workarounds.

I assume innodb_file_per_table=ON when you built the table? And is still ON? Else, all hope is lost.

Upvotes: 0

Shadow
Shadow

Reputation: 34231

First of all, indexes are stored on the disk, not in the memory. Both MyISAM and innodb may cache certain index blocks into the memory to enable faster access to the most commonly used blocks. For innodb the size of this buffer is controlled by the innodb_buffer_pool_size server system variable.

As you can see from the description, the setting of this variable is not affected by the addition or removal of indexes. So, unless you decide to increase the size of this variable, there is no direct impact of adding new index on MySQL memory usage.

Obviously, adding a new index to a large existing table will have a performance impact during the creation of the index. There will be an obvious impact after the index is added on any insert / update / delete operations, since MySQL will have to update the additional index data as well.

Upvotes: 1

Related Questions