Mysql composite indexing with tenant_id
indexing, multi-tenant, mysql
Solution
A few brief answers to the questions. As far as I can see you are confused with using `indexes`
Consider creating Indexes on columns if the Ratio -
`Consideration 1` -
(Number of UNIQUE Entries of the Columns)/(Number of Total Entries in the Column) ~= 1
That is Count of DISTINCT rows in a particular column is high.
Creating an extra `index` will always create overhead for the MySQL server, so you MUST NOT create every column an `index`. There is also a limit on number of indexes your single table can have = 64 per table
Now if your `tenant_id` is present in all the search queries, you should consider it as an `index` or in a `composite key`,
provided that -
`Consideration 2` - number of `UPDATEs` are less that number of `SELECTs` on the `tenant_id`
`Consideration 3` - The `indexe`s should be as small as possible in terms of `data types`. You MUST NOT create a `varchar 64` an index http://www.mysqlperformanceblog.com/2012/08/16/mysql-indexing-best-practices-webinar-questions-followup/
`Point to Note 1` - Even if you do declare any column an index, MySQL optimizer may still not consider it as best plan of query execution. So always use `EXPLAIN` to know whats going on. http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/
`Point to Note 2` - You may want to `cache` your search queries, so remember not to use unpredicted statements in your `SELECT` queries, such as `NOW()`
Lastly - making the PK (tenant_id, ID) should not affect the joins on your table. And an awesome link to answer all your questions in general - http://www.percona.com/files/presentations/WEBINAR-MySQL-Indexing-Best-Practices.pdf
Problem
We have a multitenant application that has a table with 129 fields that can all be used in WHERE and ORDER BY clauses. I spent 5 days now trying to find out the best indexing strategy for us, I gained lot of knowledge but I still have some questions. 1) When creating an index should I always make it a composite index with tenant_id in the first place ?(all queries have tenant_id = ? in there WHERE clause) 2) Since all the columns can be used in both the WHERE clause and the order by clause, should I create an index on them all ? (right know when I order by a column that has no index it takes 6s to execute with a tenant that has about 1,500,000 rows ) 3) make the PK (tenant_id, ID), but wouldn't this affect the joins to that table ? Any advice on how to handle this would be much appreciated. ====== The database engine is InnoDB ======= structure : ``` ID bigint(20) auto_increment primary tenant_id int(11) created_by int(11) created_on Timestamp updated_by int(11) updated_on Timestamp owner_id int(11) first_name VARCHAR(60) last_name VARCHAR(60) . . . (some 120 other columns that are all searchable) ```