MySQL "SELECT DISTINCT" Efficiency for very large tables

Question

I have a very large table (millions of records) containing approximately 8 fields as a primary key. for simplicities sake lets say that the table looks like this:

    key_1 | key_2 | key_3 | ... | key_8 | value

given a value for key_1, I need to fetch all possible values for key_2, key_3, ..., key_8 something along the following lines:

    SELECT DISTINCT key_2 FROM table1 WHERE key_1 = 123;
    SELECT DISTINCT key_3 FROM table1 WHERE key_1 = 123;
    ...
    SELECT DISTINCT key_8 FROM table1 WHERE key_1 = 123;

My problem is that this query is significantly slower then my performance needs, and the data in this table is fairly constant and rarely updated(once every few days). Also table_1 could be a slow sub-query. Short of creating an additional table in the database and manually updating it every time the database is updated, is there another solution that can give me fast results. I would need it to work across multiple MySQL Sessions.

Answer 1

SELECT DISTINCT key_2 FROM table1 WHERE key_1 = 123;

This can use your primary key index (key_1, key_2, etc.) It will perform an index scan, which is faster than a table scan or a temporary table.

SELECT DISTINCT key_3 FROM table1 WHERE key_1 = 123;

Cannot use the primary key because the combination of key_1 and key_3 don't form a prefix for the primary key. You need to create a compound index on key_1 and key_3, in that order. Then, it can use that index to perform an index scan also.

SELECT DISTINCT key_8 FROM table1 WHERE key_1 = 123;

Needs index on key_1 and key_8, in that order. Same as above.