In this post, we will discuss how to fix cardinality for InnoDB tables manually.
As a support engineer, I often see situations in which the cardinality of a table is not correct. When InnoDB calculates the cardinality of an index, it does not scan the full table by default. Instead, it looks at random pages, as determined by options innodb_stats_sample_pages, innodb_stats_transient_sample_pages, and innodb_stats_persistent_sample_pages, or by the CREATE TABLE option STATS_SAMPLE_PAGES. The default value for persistent statistics is 20. This approach works fine when the number of unique values in your secondary key grows in step with the size of the table. But what if you have a column that has a comparatively small number of unique values? This could be a common service, a many-to-many relationship table, for example, or just a table containing a list of sell orders that belong to one of a dozen shops owned by the company. Such tables could grow up to billions of rows with a small (less than 100) number of unique shop IDs.
No comments:
Post a Comment