Mysql read only engine type and indexes

indexMySQLstorage-engine

I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32).
There will only be one query run against table.

Select (column 1),(column 2) from mytable where column 3= xxxxx

Table will be dropped once a month and recreated with fresh data. Other than that, this table will be read only, and only queried with the above query.

The column that is used in the query is not unique or null.
I assume that I would add a regular index to that column. Would there be any benefit to adding indexes to the other 2 columns?

The max number of rows would be around 5,000

Also, which table engine would be best?

Best Answer

If you solve it by adding a secondary index then imho MyISAM or InnoDB won't matter much, as both use B-Tree indexes.

Append both additional columns to the index: (col3, col1, col2) or (col3, col2, col1) should behave the same for your query.

The reason to add all the columns is that such index covers your query - all data needed to resolve the query are contained inside the index, so it is not needed to read the actual rows from the table. That means the data you want to fetch are grouped together on disk (the index is ordered/clustered by col3) and no random reads to the "main" table are needed.

If you had index on only (col3) then each row may be stored in a different part of the table (depending on your primary key or order of inserts) and it would randomly jump all over the place, incurring IO penalty.

If you have no primary key, then MyISAM would be probably better, as InnoDB would create internal primary key and the table would take more space than needed.

But there is a possibility to solve it without using any secondary key. Use InnoDB table like

create table t (
   col1 varchar(5),
   col2 varchar(5),
   col3 varchar(32) not null,
   col4 smallint unsigned not null auto_increment,
   primary key(col3, col4),
   key(col4)
) engine = innodb;

That way the table will use InnoDB feature of clustering by primary key - effectively forcing the optimal order and grouping of rows described previously. And it will take just a small bit more space for the additional column (2 bytes only, if you are positive there will always be less than 60k rows) and much smaller secondary key to accomodate the auto_increment behavior instead of effectively having 2 copies of the table as with the other version.

Example: http://sqlfiddle.com/#!9/c36cfc/2

Related Question