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

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:

  FROM search
 WHERE S.c = 12
   AND S.f = 'foo'
   AND S.h = 'bar'
   AND S.a < 1234567890
 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?

  • 3
    Please add more examples of queries. For this one, an index on (c, f, h, a) would be optimal. The 8-column index, not so much. Jul 6 '17 at 11:27
  • 2
    General rule is: you need an index that include all the columns used in the WHERE and ORDER BY clauses. Columns should be arranged in the index' definition from the most selective to the less selective one.
    – Kondybas
    Jul 6 '17 at 12:13
  • 1
    @Aeveus So try to arrange columns in the index by selectivity. Selectivity is proportional to the number of distinct values column can contain. For boolean column that can be only 0 or 1 selectivity is 2. For column containing day of week selectivity is 7(8). Timestamps have selectivity close to the number of rows in the table. Therefore column in the index should be ordered (timestamp, weekday, boolean). You can find the selectivity of columns by SELECT COUNT(DISTINCT colname) FROM table;
    – Kondybas
    Jul 6 '17 at 12:29
  • 1
    Also, while all columns may be used in searches, are some columns more common than others? Even a highly selective column index won't help if the column only shows up in the WHERE clause of 1% of all queries.
    – RDFozz
    Jul 6 '17 at 15:38
  • 1
    @ypercubeᵀᴹ your comment should be expand into an answer. As you said, more query examples would be needed to determine additional index combinations. Jul 6 '17 at 15:53

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.