Paradoxically, an index is only sometimes the fastest way to access data! The MySQL optimizer will always try to pick the quickest way to get the data it needs, which sometimes means choosing a slightly counter-intuitive method. Usually, you won't need to worry about the table sampling or updating the statistics manually. This is beyond the scope of this article, but the MySQL documentation has a comprehensive page on the topic. If you find that the statistics need to be more accurate for a particular table and are continually causing the optimizer to make poor decisions, you can change how the sampling is done. If the statistics are so outdated that it is causing problems, you can force an update by running ANALYZE TABLE. This happens in the background, and you shouldn't ever notice it. These statistics are automatically updated after 10% of a table has changed. The Cardinality column shows you the stored value MySQL will use to make its selectivity decisions. | people | 1 | state | state | A | 1 | BTREE | YES | You can see this stored value by running SHOW INDEXES from : It would be inefficient for MySQL to calculate that each time, so instead, it keeps track of the cardinality over time using random sampling. We've been calculating cardinality by running COUNT(DISTINCT column) each time. To speed this choice up, MySQL keeps statistics about the data's rough shape, which can become outdated. So while checking average selectivity is a good rule of thumb, pay careful attention to unevenly distributed data.Īll other things being equal, MySQL will choose the most selective index possible. But when you're querying for admins, it is highly selective. In this case, an index on type might seem useless because it's not very selective on average. In some cases, an index might be highly selective for one query and not selective at all for another.Ĭonsider a table of one million users, where 99% are of type = "user" and 1% are of type = "admin". | 1.0000 | 0.0060 | 0.0000 | Selectivity is query-dependentĬalculating selectivity across an entire table can be misleading if the data is not evenly distributed. In the case where multiple indexes might work, the optimizer must make a decision between multiple viable options.Ĭonsider the following query, which searches for people named "Aaron" that live in Texas: In our example above, only one possible index could satisfy the query, so the optimizer doesn't have to choose which one is best. Now that we know how to determine what index is (or isn't!) being used, let's look at some of the reasons why your index might not be used. Having decided on the indexes that apply to the query, it must then choose between those indexes as to which one is the most efficient. MySQL's query optimizer, responsible for determining the best way to execute a query, looks at the query and the available indexes and decides which indexes are applicable. These are separate pieces of information, both of which are valuable! Before your index can be chosen, it must first be considered. Our index on first_name was considered, and it was chosen. Check out this article for more a more in-depth guide on how to read an EXPLAIN output. If you have an expression you use frequently, you can extract it into a function: import type ). If you need to apply runtime transformations to the returned value, you can use the. use sql for a field that will be returned as a string), the runtime value won’t match the expected type.ĭrizzle cannot perform any type casts based on the provided type generic, because that information is not available at runtime. By specifying sql, you are telling Drizzle that the expected type of the field is string.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |