Which indices should be added to speed up queries on massive InnoDB table?

Here is my table:

CREATE TABLE `letters` (
  `a` bigint(20) unsigned NOT NULL,
  `b` bigint(20) unsigned NOT NULL,
  `c` bigint(20) unsigned NOT NULL,
  `d` bigint(20) unsigned NOT NULL,
  `e` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

• The table will have about 1+ billion rows.
• Each column can be queried; each column can be referenced. e.g.:
  • SELECT [any column] FROM letters WHERE [any / any other column] IN ([subquery or list]);

My question: what indices should I add to speed up any query in the format above? (Also, if possible, please try to describe 'why' it/they should be added so that I can learn from your answer).

Thanks!

-- Extra info: inserts will happen on a fairly regula开发者_运维百科r basis (a few/handful every second) but select queries will happen more frequently.

Since any column can appear in the WHERE clause, you've to add an index for each of the column, except for the field a since is already the PRIMARY KEY and as such is already indexed.

UPDATE: as for the subsequent discussion, Poodlehat pointed out that the column e has a low index selectivity, i.e. "The ratio of the number of distinct values in the indexed column / columns to the number of records in the table". For this reason, it's not clear whether adding an index on column e will help or slow down queries. So Lucas will try experimentally and hopefully share the results to us.

I think you need to have a unique index on a (or it should be a primary key), definitely indexes on b,c,d (on each). No need for index on e (it won't be used anyway since as you say it has just 15 different values)