MySQL Indexing and performance when size of index is greater than size of data in table

Question

I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine.

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) 
'Size in MB', 
IF(index_length > data_length, 1, 0) as poorIndexing
FROM information_schema.TABLES 

The results are that some of the tables have much larger index_lengths than their actual data table. I was under the impression that this meant that MySQL would skip over the indexes and simply do a full table scan because there is less data to parse in the entire table than the index.

However, is this true if an individual index is smaller than the aggregate? I.e. if I'm doing something like "SELECT * FROM user WHERE id = 244", and the id is indexed, but so is the address, phone, username, email, etc. does InnoDB just use the id or does MySQL see the size of the entire operation and simply skip over it. Or is it only a problem if the query is :

SELECT username FROM users WHERE username = 'johnrambothe14th';

And the total amount of indexes for username is greater than the total amount of data?

Answer 1

As you mentioned of 5.6 i guess tables are Innodb... Where we have PK stored as clustered index. The rows are stored in an index or say leaf node of the PK index is data itself.

Secondary further contains the PK key columns as well. So a long PK key => longer Secondary index (WRT size).

About large indexes, you might need to look out for duplicate indexes. Also one thing to make sure is having large enough innodb-buffer-pool to make sure indexes are in memory for quicker access.

Refer:

• https://dev.mysql.com/doc/refman/5.6/en/how-to-avoid-table-scan.html
• https://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html
• https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

Answer 2

Single index cannot be bigger than the entire table (unless it contains all columns and is badly fragmented).

With your query you only get the sum of sizes of all indexes defined, but a query can (with some small exceptions) use only one index per query and table so the size of the specific index is what matters, not the sum.

When optimizer decides what index to use, it does some estimation of rows and pages to read from disk/memory. For the right index and direct ref/range access by a specified value, the number of pages to read is really small compared to the entire size of the index. In the case no index exists to get the direct access, the index scan is only attempted if it gives any positive effect like if the order is right so no additional sorting is done or if all columns the query needs are contained in the index, so the underlying table is not needed - as the specific index will be smaller than the entire table or in the worst case the same size, it can be used - thats called covering index.

Answer 3

(The other answers are valid, but here is more to chew on.)

1. Your two examples will use, some index starting with id and with username, respectively.

2. But the Optimizer will shun the index if more than, say, 20% of the table has that id or username.

3. However, if the index is "covering" (that is all the columns needed for the SELECT are in the index), it will use the index.

(There are more exceptions, but those cover most cases.)

SELECT * FROM user WHERE id = 244

Assuming InnoDB and PRIMARY KEY(id), then the index is "clustered" with the data. It will use the PK.

SELECT username FROM users WHERE username = 'johnrambothe14th';

For this, INDEX(username) qualifies as "covering", so the index only will be used. EXPLAIN will say that by saying Using index.

SELECT username, id FROM users WHERE username = 'johnrambothe14th';

Is also "covering" because every secondary index in InnoDB silently contains the PK.

SELECT username, foobar FROM users WHERE username = 'johnrambothe14th';

is not covered by INDEX(username), but would be covered by INDEX(username, foobar). Either index could be beneficial; the latter would be better.