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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With