If you're creating a multi-tenant SaaS application using Ruby on Rails, then this advice will be helpful to you. I'm going to be keeping this specific to MySQL, although you should be able to follow a similar advice for Postgres too.
How does MySQL use indexes
Imagine you have a table called "posts" representing blog posts. It has two columns of interest:
- tenant_id: This is the tenant to which the post belongs. This could be name anything in your system, say, organization_id.
- post_type_id: This refers to an entity in another table that represent "post types." It is very important to note that post_types are common for all tenants.
Also, assume that both
post_type_id have individual non-unique indexes.
When you do a query that uses both
post_type_id in a where clause, MySQL would do one of the three:
- It will use an
index_merge, trying to use both
post_type_id. However, the estimated rows scanned could be much more than what the query planner reports. See this post from Percona for a detailed analysis.
- It will use either the index on
tenant_id, or the one on
post_type_id, depending on which one is estimated to have a higher cardinality. However, it will not use both.
What's wrong with independent indexes?
Let's assume that you have very few tenants. So, the number of distinct
tenant_id is lower. Whenever you perform a query that has both
post_type_id in the
where clause, it will most likely select the index on
post_type_id because it will provide for more selective, i.e., fewer rows to scan.
However, some day, you'd have more tenants, probably much more than the number of post types. In that case, MySQL will abruptly switch to using the index on
Not having a deterministic execution plan is not a good thing, because your queries could suddenly become slow because of how the data has changed, even if the resultset is still the same size.
Using composite indexes
If you have a composite index on
(tenant_id, post_type_id) instead, then MySQL would (most likely) always choose this composite index, and will have a deterministic selectivity.