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