MySQL: which is preferable, separate indexes or same index?

Question

I'm trying to speed up SELECT queries in MySQL tables which already have some indexes. Hopefully without significant impact on frequent INSERT INTO queries running at the same time (by other clients). The tables have millions of entries and have already passed the Gigabyte size (they have a BLOB field).

The tables have a pair of fields describing a contiguous interval (can be a time interval in microseconds, or a number of execution, or version, or whatever). These two fields are indexed; I have two indexes, one for only one of the fields (when I query on it), and another for both fields (which can also appear on many queries).

Two other candidate indexes come from an identifier (for example, filename), since it's commonly used in queries as well.

A colleague proposes that we incorporate the identifier in the two previous indexes. However, I have the feeling it makes more sense to have a separate key. I'm preparing a test to ascertain which is the better option, but I expect it will take a couple of days. In the meantime, I'm looking for past experiences and/or advice in regard to this.

So my questions are:

1. Which is ideal, create a new index for the identifier? Or incorporate it in the interval index? I.e., many identifiers can have the same interval, and many intervals can have the same identifier.
2. Which of the two options should consume more disk space, especially in multi-million-row tables? I believe the "faster" one, whichever it will be, will be also the most space-consuming.
3. I don't know whether creating the new index (for example, the separate index option) solves nothing or is of little help. What are your past experiences on this or similar situations? Any advice/documentation I can research specifically on this subject?

Answer 1

Determining which indexes to define for a table depends on a lot of factors. One of the most important of them is what queries exactly you want to run.

In this case, without knowing the table structure and exactly how the queries you want to include the identifier in work, it is hard to say how to build the indexes.

Keep in mind that MySQL has a one index per query limit. This means that the query optimizer will only use one index (if at all) for any given table at a time.