Deciding on optimal index combinations I have a table with 8 columns, no primrary key, and many, many indexes. Now I wonder whether they're as optimal as can be.

The table looks as follows:

CREATE TABLE `search` (
  `a` bigint(20) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


This table is used for searches, and searches contain constant values to look for, and a specific range of two columns (i.e. a, and b). Never together.

An example of a query that occurs very often:

SELECT DISTINCT S.a
  FROM search
 WHERE S.c = 12
   AND S.f = 'foo'
   AND S.h = 'bar'
   AND S.a &lt; 1234567890
 ORDER BY S.a DESC
 LIMIT 200;


An was to have two composite indexes, containing all columns used for constant comparisons, with a single range column appended to it. This resulted in the following two indexes:

KEY composite_a (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `a`)
KEY composite_b (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `b`)


However, this feels excessive, and it still resorts to temporary tables, and file sorting if only a subset of the columns is used for searching.

A different option was to add every single combination with the range columns appended to it (which works quite well), but fills up storage rather quickly. To give a sense of size, it's a table containing a few billion rows. This caused the need for a significantly larger machine with more memory, because of indexes growing into the several terabytes range.

My question is: am I missing an index 'trick'? Is there an efficient combination of index to solve this problem?
That one example needs

INDEX(c, f, h,  -- these can be in any order
      a,        -- this must be after the first 3
      ... )     -- others can follow


Your suggestions with (c, d, ... are not very useful.

If you are expecting to have lots of different queries, I recommend you


Study what is actually used by users.  You will find that there are a few common columns or pairs of columns, and some columns that are rarely used.
Then add a few indexes, no more than, say, a dozen.
Generally don't make indexes with more than 3 columns.
Have the indexes scrambled around.  The two you suggested may as well be replaced by simply (c,d,e,f,g,h,i) since this will be quite useful for both.
Study this for how to generate the optimal index for a given select.