performant ordering of keys in a MySQL compound index (WRT Rails Polymorphic associations and STI) -

previously, asked this question compound indexes on polymorphic foreign keys in activerecord. basis of question understanding indexes should based on cardinality of column, , there's pretty low cardinality on rails's sti type , polymorphic _type columns.

accepting answer question right -- that's there's value indexing both high cardinality _id columns , low cardinality _type columns, because have high cardinality -- next question is: how should order compound indexes?

an index of [owner_id, owner_type] places field higher cardinality first, while [owner_type, owner_id] places field higher cardinality second. query using former key more performant query using latter key, or equally performant?

i ask because has particular bearing on how order compound keys tables serving sti models. sti rails finders query on type column -- again column of low cardinality. type column therefore queried more other indexes. if type column queried more often, maybe makes sense use type-leading index, because less specific queries take advantage of first part of index yielding performance-boost. however, wouldn't smaller perk come @ detriment of performance highly-specific queries. take advantage of higher-cardinality portion of index.

from own research (but i'm no expert dba) i've learned there's 2 thing consider when deciding order of compound key index.

first, concerning cardinality of columns, index better @ searching columns high cardinality. inclined place column highest cardinality first in index. reference, there's article titled mysql query optimization says:

indexes work best columns have high cardinality relative number of rows in table (that is, columns have many unique values , few duplicates).

in case, _id columns fit better definition, they're better candidate being prefix of key.

another thing consider reusability of these indexes. (if not all) database systems allow prefix of compound key reused. example, compound key on (owner_id, owner_type) used queries on owner_id not on owner_type.

so explained in question might better off 2 indexes: compound key index on (owner_id, owner_type) , on (owner_type).

finally, comes down dataset , queries. try out multiple scenarios, benchmarks using different compound key ordering see optimal solution. also, don't forget indexes incur write penalty on tables.

update: there's rather popular question compound key index there:

when should use composite index?