gasilbusy.blogg.se

26 Oktober 2022 - 01:35
Mysql optimizer
Allmänt
Mysql optimizer








mysql optimizer

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | In other words, for each row in t1, there is one matching row in t2:Ĭon1> EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.i=t2.j If we look at the query plan for a join of the two tables, we see that the rows column for t2 contains 1. | j | size | 1 | Number of pages in the index | | j | n_leaf_pages | 1 | Number of leaf pages in the index | | PRIMARY | size | 1 | Number of pages in the index | | PRIMARY | n_leaf_pages | 1 | Number of leaf pages in the index | | index_name | stat_name | stat_value | stat_description |

mysql optimizer

> FROM mysql.innodb_index_stats WHERE table_name = 't2' If we look at the persistent statistics, we see that both of t2's indexes contains 100 unique values:Ĭon1> SELECT index_name, stat_name, stat_value, stat_description ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciĬon1> INSERT INTO t2 SELECT i, j FROM t1 Īt this point, both tables, t1 and t2, will have 100 rows with unique values for both columns i and j. We will use the following table for the experiment: When does InnoDB's recalculation of statistics become visible? If you are not interested in my proof for this, you may skip to the end of the blog where I will provide some recommendations for running ANALYZE TABLE. The conclusion is that there are really no guarantees as to when the query optimizer will see statistics refreshed by InnoDB, and that you need to regularly run ANALYZE TABLE to guarantee this.

  • When a new connection accesses the table, the updated statistics will be visible to all connections.Īs you already may have guessed, none of these statements are actually true, and below I will present a small experiment that shows this.
  • The updated statistics will only be visible to new connections (i.e., sessions that are started after the statistics was updated).
    • I must admit that I, for a long time, believed in the myths I was told about when updates to InnoDB's index statistics become visible to the query optimizer. However, the matter is even worse! In this blog post I will show that even when the automatic recalculation is performed, there are no guarantees as to when the server will see the changes.

    #MYSQL OPTIMIZER UPDATE#

    This blog post is inspired by Jesper's recent blog post on how the automatic update of InnoDB persistent statistics may never trigger if servers are restarted frequently.










    Mysql optimizer
    0 kommentar(er)
    Om Mig: