OpenWord 2017: MySQL 8.0: What’s New in the Optimizer

Session was presented by Manyi Lu and Oystein Grovlen from Oracle and provided a little more depth to topics covered in previous presentations I attended.

  • CTE
    • can precede SELECT/UPDATE/DELETE including sub-queries
    • Can be used to refer to other CTEs (not able to do that with direct tables)
    • Recursive CTEs
      • set cte_max_recursion_depth to limit runaway recursion (default 100)
  • Window Functions
    • OVER (PARTITION BY ……)
  • UTF-8 Support (utf8mb4)
    • utf8mb4_0900_ai_ci default collation
    • performance gains
    • recommend utf8mb4_unicode_520_ci if prior to MySQL 8.0
    • careful with migrated tables can lead to
      • “illegal mix of collation” issues
      • slower queries as index will not be used
  • GIS
    • Geography Support
      • longitude/latitude
    • Spatial Reference Systems
  • JSON
    • Aggregation functions – combine JSON documents in multiple rows into a JSON array
    • Convert JSON documents to relational tables – allows access to sql aggregations and other methods
    • Utility Functions
      • JSON_PRETTY pretty print
      • JSON_STORAGE_SIZE – shows bytes used to store doc
      • JSON_STORAGE_FREE – shows bytes not used
  • Invisible Index
  • Descending indexes
  • Cost Model improvements
    • adapt to SSSD, larger memories, caches
    • cost model takes into account where data is stored
  • Histograms
  • Join Order Hints

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s