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.
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 < 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?