MySQL Indexing and performance when size of index is greater than size of data in table 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 &gt; 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?
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