CODEWITHSUNDEEP
mysqldatabaseoptimizationindexinginnodb
Peter Lindqvist
Peter Lindqvist

Reputation: 10200

MySQL Index is bigger than the data stored

I have a database with the following stats

Tables     Data   Index   Total
11     579,6 MB  0,9 GB  1,5 GB

So as you can see the Index is close to 2x bigger. And there is one table with ~7 million rows that takes up at least 99% of this.

I also have two indexes that are very similar

a) UNIQUE KEY `idx_customer_invoice` (`customer_id`,`invoice_no`),
b) KEY `idx_customer_invoice_order` (`customer_id`,`invoice_no`,`order_no`)

Update: Here is the table definition (at least structurally) of the largest table

CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL,
  `order_no` varchar(10) default NULL,
  `invoice_no` varchar(20) default NULL,
  `customer_no` varchar(20) default NULL,
  `name` varchar(45) NOT NULL default '',
  `archived` tinyint(4) default NULL,
  `invoiced` tinyint(4) default NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `group` int(11) default NULL,
  `customer_group` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_customer_invoice` (`customer_id`,`invoice_no`),
  KEY `idx_time` (`time`),
  KEY `idx_order` (`order_no`),
  KEY `idx_customer_invoice_order` (`customer_id`,`invoice_no`,`order_no`)
) ENGINE=InnoDB AUTO_INCREMENT=9146048 DEFAULT CHARSET=latin1 |

Update 2:

mysql> show indexes from invoices;
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| invoices |          0 | PRIMARY                    |            1 | id          | A         |     7578066 |     NULL | NULL   |      | BTREE      |         |
| invoices |          0 | idx_customer_invoice       |            1 | customer_id | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| invoices |          0 | idx_customer_invoice       |            2 | invoice_no  | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_time                   |            1 | time        | A         |      541290 |     NULL | NULL   |      | BTREE      |         |
| invoices |          1 | idx_order                  |            1 | order_no    | A         |        6091 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            1 | customer_id | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            2 | invoice_no  | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            3 | order_no    | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

My questions are:

  1. Is there a way to find unused indexes in MySQL?
  2. Are there any common mistakes that impact the size of the index?
  3. Can indexA safely be removed?
  4. How can you measure the size of each index? All I get is the total of all indexes.

Upvotes: 19

Views: 12386

Answers (6)

ni hao
ni hao

Reputation: 11

table size where index size is more than data size

Executed below two commands to analyze and optimize

    analyze table <table name>;
    optimize table <table name>;

table size where now index size is less than data size

I hope this helps if you are wondering why index size is more than total data size.

Upvotes: 1

Michal Jajcaj
Michal Jajcaj

Reputation: 308

I may be wrong, but the first index (idx_customer_invoice) is UNIQUE, the second (idx_customer_invoice_order) is not, so you'll probably lose the uniqueness constraint when you remove it. No?

Upvotes: 9

Neo
Neo

Reputation: 5463

  1. indexA can remove because there's a indexB include indexA
  2. what impact your index length is your column type and column length

  3. use:

    select index_length from information_schema.tables where table_name='your_table_name' and table_schema='your_db_name';

    get your table index_length

Upvotes: 1

dkretz
dkretz

Reputation: 37655

You can remove index A, because, as you have noted, it is a subset of another index. And it's possible to do this without disrupting normal processing.

The size of the index files is not alarming in itself and it can easily be true that the net benefit is positive. In other words, the usefulness and value of an index shouldn't be discounted because it results in a large file.

Index design is a complex and subtle art involving a deep understanding of the query optimizer explanations and extensive testing. But one common mistake is to include too few fields in an index in order to make it smaller. Another is to test indexes with insufficient, or insufficiently representative data.

Upvotes: 14

Nemoden
Nemoden

Reputation: 9056

use 

show indexes from table;

to define what indexes do you have in a particular table. Cardinality would tell how useful your index is.

You can remove your indexes safely (it will not break a table), but beware: some queries might execute slower. First you should analyze your queries to decide whether you need a certain index or not.

I don't think you can find out data length of a particular index, though.

BUT, I think you probably think that if indexes length is greater than data length twice is something abnormal... Well, you are wrong. All of your indexes might be useful ;) If you have a table that provides a lot of information and you have to search on it upon a large number of column, it can easily be that indexes of this table will 2 times bigger in size that the tables data.

Upvotes: 1

Benoit
Benoit

Reputation: 79185

Is there a way to find unused indexes in MySQL?

The database engine optimizer will select a proper index when attempting to optimize your query. Depending on when you collected statistics on your indexes last, the index which is chosen will vary. Unused indexes could suddenly become used because of new data repartition.

Can indexA safely be removed?

I would say yes, if indexA and indexB are B-Tree indexes. This is because an index that starts with the same columns in the same order will have the same structure.

Upvotes: 1

Related Questions