Fulfillment of key orders in the composite index MySQL (WRT Rails Polymorphic associations and STI)

Question

I used to ask this question about composite indexes for polymorphic foreign keys in ActiveRecord . The basis of my question was my understanding that indexes should be based on the power of your column and, as a rule, rather low power in Rails STI and polymorphic columns like _type.

Making the decision that the answer to my question is right - what is the value for indexing high power columns _id and columns of low power columns, since they are high power together - my next question is: how should you order your composite indexes?

The index [owner_id, owner_type] first places the field with higher power, and [owner_type, owner_id] puts the field with higher power per second. Is a query using the previous key more effective than a query using the last key, or are they equally effective?

I ask because it is of particular importance for how I order composite keys for tables serving STI models. STI Rails crawlers almost always query in a type column, which again is a low power column. Therefore, a type column is requested much more often than other indexes. If a type column is queried much more often, then it might make sense to use an index leading to the type, because less specific queries can take advantage of the first part of the index, which gives better performance. However, I would not become smaller to get at the expense of performance for very specific queries. which take advantage of a higher index power.

Answer 1

From my own research (but I'm not a DBA expert), I learned that there are two things to consider when deciding on a composite key index.

First, with regard to column power, an index is usually better suited for finding high power columns. Therefore, I would be inclined to put the column with the highest power first in the index. For reference, there is an article called MySQL Query Optimization , which states:

Indexes work best for high power columns compared to the number of rows in the table (i.e. columns with many unique values ​​and multiple duplicates).

In your case, the _id columns will better fit this definition, so they will be the best candidate for a key prefix.

Another thing to consider is the reuse of these indexes. Most (if not all) database systems allow you to use the composite key prefix for reuse. For example, the composite key on (owner_id, owner_type) can also be used by requests on owner_id , but not on owner_type .

So, from what you explained in your question, you might be better off with two indexes: the composite key index at (owner_id, owner_type) and the other at (owner_type) .

Finally, it really comes down to your datasets and queries. Try several scenarios, tests using different composite keys to find out what is the most optimal solution. Also, do not forget that indexes are penalized for writing to your tables.

Refresh . There is also another fairly popular question about a complex key index:

When should I use a composite index?

Answer 2

TL; DR First type, then id.

True, first putting the identifier should increase the power of the first solution, which makes it easy to scan the resulting records or apply a second small index. However, if you ever only query by type (what you need), you will have to maintain another top-level index for the type, which will give you a performance hit when writing.

Another method, [type, id] , will give a top-level index that can be reused when searching only by type. The second solution will always match a single row, since the identifier is unique in type, so you are still not sure about scanning rows after index resolution.

IMO, a hit on recording performance that supports a different index, is not worth the marginal gain in that it does not first accept a decision tree of type.