Frédéric Descamps Community Manager Oracle MySQL Czechia & Slovakia Tour - March 2025 MySQL Indexes and Histograms How They Really Speed Up Your Queries 

Who am I ? about.me/lefred Copyright @ 2025 Oracle and/or its affiliates. 2 

• @lefred • @lefredbe.bsky.social • @[email protected] • MySQL Evangelist • using MySQL since version 3.20 • devops believer • living in • h�ps://lefred.be Frédéric Descamps Copyright @ 2025 Oracle and/or its affiliates. 3 

What Is This Session About ? Copyright @ 2025 Oracle and/or its affiliates. 4 

Speed and Performance ! Nobody ever complains that the database is too fast ! Copyright @ 2025 Oracle and/or its affiliates. 5 

Speed and Performance ! Nobody ever complains that the database is too fast ! It's often the reverse... blame the database ! Copyright @ 2025 Oracle and/or its affiliates. 5 

Speed and Performance ! Nobody ever complains that the database is too fast ! It's often the reverse... blame the database ! Speeding up queries is not a 'dark art'... but understanding how to speed up queries is often treated as magic. Copyright @ 2025 Oracle and/or its affiliates. 5 

Speed and Performance ! Nobody ever complains that the database is too fast ! It's often the reverse... blame the database ! Speeding up queries is not a 'dark art'... but understanding how to speed up queries is often treated as magic. So we will be looking at the proper use of indexes, histograms and see how to keep the rigth balance for your workload. Copyright @ 2025 Oracle and/or its affiliates. 5 

• No coverage today of: ◦ System Con�guration ▪ OS ▪ MySQL ◦ Hardware ◦ Networking/Cloud This is a dry subject ! • Do not try to absorb all the content at once, get the slides (h�ps://speakerdeck.com/ lefred) Copyright @ 2025 Oracle and/or its affiliates. 6 

The North Star Query Respone Time Copyright @ 2025 Oracle and/or its affiliates. 7 

Daniel Nichter, E�cient MySQL Performance - Best Practices and Techniques, O'Reilly, 2021 Query Response time Query response time is the only metric anyone truly cares about [...] because query response time is the only metric we experience. When a query takes 7.5 seconds to execute, we experience 7.5 seconds of impatience. That same query might examine a million rows, but we don't experience a million rows examined. Our time is precious.(*) Copyright @ 2025 Oracle and/or its affiliates. 8 

Today's goal is to... reduce the Query Response Time Copyright @ 2025 Oracle and/or its affiliates. 9 

Bad Queries Finding the Ugly Duckling Copyright @ 2025 Oracle and/or its affiliates. 10 

What are bad queries ? We can de�ne bad queries in two di�erent categories: Copyright @ 2025 Oracle and/or its affiliates. 11 

• queries called way to often • queries that are way too slow ◦ full table scan ◦ using �lesort ◦ using temporary tables What are bad queries ? We can de�ne bad queries in two di�erent categories: Copyright @ 2025 Oracle and/or its affiliates. 11 

What are bad queries ? Copyright @ 2025 Oracle and/or its affiliates. 12 

If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). Copyright @ 2025 Oracle and/or its affiliates. 13 

If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to �nd that Ugly Duckling: Copyright @ 2025 Oracle and/or its affiliates. 13 

If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to �nd that Ugly Duckling: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2025 Oracle and/or its affiliates. 13 

If there could be only one ? If you should optimize only one query, the best candidate should be the query that consumes the most of the execution time (seen as latency in PFS, but usually called "response time"). sys Schema contains all the necessary info to �nd that Ugly Duckling: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2025 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * schema_name: piday schema_name: piday tot_lat: tot_lat: 4.29 4.29 h h exec_count: exec_count: 5 5 latency_per_call: latency_per_call: 51.51 51.51 min min query_sample_text: query_sample_text: select select a a. .device_id device_id, , max max( (a a. .value value) ) as as ` `max temp max temp` `, , min min( (a a. .value value) ) as as ` `min temp min temp` `, , avg avg( (a a. .value value) ) as as ` `avg temp avg temp` `, , max max( (b b. .value value) ) as as ` `max humidity max humidity` `, , min min( (b b. .value value) ) as as ` `min humidity min humidity` `, , avg avg( (b b. .value value) ) as as ` `avg humidity avg humidity` ` from from temperature_history a temperature_history a join join humidity_history b humidity_history b on on b b. .device_id device_id= =a a. .device_id device_id where where date date( (a a. .time_stamp time_stamp) ) = = date date( (now now( () )) ) and and date date( (b b. .time_stamp time_stamp) )= =date date( (now now( () )) ) group group by by device_id device_id _ _ 13 

More info about Queries Sys Schema contains all the required information in these tables : • statements_with_full_table_scans • statements_with_runtimes_in_95th_percentile • statements_with_sorting • statements_with_temp_tables Copyright @ 2025 Oracle and/or its affiliates. 14 

More info about Queries Sys Schema contains all the required information in these tables : • statements_with_full_table_scans • statements_with_runtimes_in_95th_percentile • statements_with_sorting • statements_with_temp_tables And since MySQL 8.0 you can join the table performance_schema.events_statements_summary_by_digest to have a sample you can use. Copyright @ 2025 Oracle and/or its affiliates. 14 

More info about Queries Sys Schema contains all the required information in these tables : • statements_with_full_table_scans • statements_with_runtimes_in_95th_percentile • statements_with_sorting • statements_with_temp_tables And since MySQL 8.0 you can join the table performance_schema.events_statements_summary_by_digest to have a sample you can use. We will check the meaning of this tables in some slides... be patient ;) Copyright @ 2025 Oracle and/or its affiliates. 14 

Copyright @ 2025 Oracle and/or its affiliates. 15 

The Optimizer some theory Copyright @ 2025 Oracle and/or its affiliates. 16 

The MySQL Optimizer • Consider the Optimizer the brain and nervous system of MySQL • Query optimization is a feature of many Relational Database Management Systems • The query optimizer a�empts to determine the most e�ective way to execute a given query by considering the possible query plans (h�ps://en.wikipedia.org/wiki/Query_optimization) Copyright @ 2025 Oracle and/or its affiliates. 17 

The MySQL Optimizer - estimation • One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans. • These costs are the result of a mathematical model of query execution costs that relies heavily on estimates of the cardinality, or number of tuple, �owing through each edge in a query plan. Copyright @ 2025 Oracle and/or its affiliates. 18 

The MySQL Optimizer - evaluation of the options • The Optimizer wants to get your data the cheapest way possible. • Like a route planner, the cost is built on historical statistics. And these statistics can change while the optimizer is working. So like a tra�c jam, washed out road, or other tra�c problem, the optimizer may be making poor decisions for the present situation... but this is very rare ! • The �nal determination from the optimizer is called the Query Execution Plan (or QEP, or Query Plan). • MySQL wants to optimize each query every time it sees it (there is no locking down the query plan like Oracle). Copyright @ 2025 Oracle and/or its affiliates. 19 

120 if your query has �ve joins the optimizer may have to evaluate 120 di�erent options 5! (5 * 4 * 3 * 2 * 1) Copyright @ 2025 Oracle and/or its affiliates. 20 

The Query Execution Plan EXPLAIN: DBAs companion Copyright @ 2025 Oracle and/or its affiliates. 21 

EXPLAIN is the command used to obtain the Query Execution Plan for a query including information about how tables are joined and in which order, which indexes are used and estimation of rows, ... EXPLAIN Syntax Copyright @ 2025 Oracle and/or its affiliates. 22 

EXPLAIN Example Copyright @ 2025 Oracle and/or its affiliates. 23 

EXPLAIN Example this is an ESTIMATION on how MySQL would run the query as it is not executed ! Copyright @ 2025 Oracle and/or its affiliates. 23 

• system: the table contains excatly 1 row • const: at most 1 row is matched for the table • eq_ref: the table is the right-hand table in a join where the condition is on a PK or not null unique key. • ref: the table is �ltered by a nonunique secondary index. • ref_or_null: the same as ref but the �ltered column may also be NULL. • index_merge: the Optimizer chooses a combination of two or more indexes to resolve a �lter that includes an OR or AND between columns in di�erent indexes. • fulltext: use of a full text index to �lter the table. • range: this is used when an index is used to look up several values either in sequence or in groups. EXPLAIN - Access type Copyright @ 2025 Oracle and/or its affiliates. 24 

• index: the Optimizer chosen to perform a full index scan. • ALL: full table scan !! EXPLAIN - Access type (2) Get much more info an examples in Chapter 20, Analyzing Queries from Jesper Wisborg Krogh's book: MySQL 8 Query Performance Tuning, Apress, 2020. Copyright @ 2025 Oracle and/or its affiliates. 25 

EXPLAIN Example (with a JOIN) Copyright @ 2025 Oracle and/or its affiliates. 26 

Visual EXPLAIN Example from MySQL Workbench Copyright @ 2025 Oracle and/or its affiliates. 27 

EXPLAIN FORMAT=TREE Example Copyright @ 2025 Oracle and/or its affiliates. 28 

EXPLAIN FORMAT=TREE Example Copyright @ 2025 Oracle and/or its affiliates. 28 

EXPLAIN FORMAT=JSON Example Copyright @ 2025 Oracle and/or its affiliates. 29 

EXPLAIN FORMAT=JSON Example this is the most detailed estimation ! Copyright @ 2025 Oracle and/or its affiliates. 29 

More on EXPLAIN FORMAT Since MySQL 8.2 you can de�ne the default format of EXPLAIN using the variable explain_format: SQL SQL > > select select @ @@explain_format @explain_format; ; + +------------------+ ------------------+ | | @ @@explain_format @explain_format | | + +------------------+ ------------------+ | | TRADITIONAL TRADITIONAL | | + +------------------+ ------------------+ 1 1 row row in in set set ( (0.0002 0.0002 sec sec) ) SQL SQL > > set set explain_format explain_format= =tree tree; ; It's alo possible to use EXPLAIN FOR SCHEMA now. Copyright @ 2025 Oracle and/or its affiliates. 30 

More on EXPLAIN FORMAT=JSON With MySQL 8.3 you have as second JSON ouput format. This is con�gurable using explain_json_format_version: SQL SQL > > set set explain_json_format_version explain_json_format_version= =2 2; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0001 0.0001 sec sec) ) SQL SQL > > explain explain format format= =json json select select * * from from city city join join country country on on ( (city city. .population population = = country country. .population population) )\G \G Copyright @ 2025 Oracle and/or its affiliates. 31 

EXPLAIN FORMAT=JSON version 2 Copyright @ 2025 Oracle and/or its affiliates. 32 

EXPLAIN to a variable With JSON format, it's now also possible to save the EXPLAIN's output to a variable: SQL SQL > > explain explain format format= =json json into into @myqep @myqep select select * * from from city city join join country country on on ( (city city. .population population = = country country. .population population) ) ; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0003 0.0003 sec sec) ) SQL SQL > > select select json_extract json_extract( (@myqep @myqep, , "$.estimated_total_cost" "$.estimated_total_cost") ) as as query_cost query_cost; ; + +-------------------+ -------------------+ | | query_cost query_cost | | + +-------------------+ -------------------+ | | 96473.87597408294 96473.87597408294 | | + +-------------------+ -------------------+ 1 1 row row in in set set ( (0.0001 0.0001 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 33 

can we know the real numbers ? Copyright @ 2025 Oracle and/or its affiliates. 34 

• Estimated cost • Actual execution statistics ◦ Time to return �rst row ◦ Time to return all rows ◦ Number of rows returned ◦ Number of loops EXPLAIN ANALYZE Copyright @ 2025 Oracle and/or its affiliates. 35 

• Estimated cost • Actual execution statistics ◦ Time to return �rst row ◦ Time to return all rows ◦ Number of rows returned ◦ Number of loops Instruments and executes the query EXPLAIN ANALYZE Copyright @ 2025 Oracle and/or its affiliates. 35 

EXPLAIN ANALYZE Example Copyright @ 2025 Oracle and/or its affiliates. 36 

EXPLAIN ANALYZE Example Copyright @ 2025 Oracle and/or its affiliates. 37 

EXPLAIN ANALYZE Example time is expressed in milliseconds Copyright @ 2025 Oracle and/or its affiliates. 37 

EXPLAIN ANALYZE Example Copyright @ 2025 Oracle and/or its affiliates. 38 

EXPLAIN ANALYZE Example Copyright @ 2025 Oracle and/or its affiliates. 39 

EXPLAIN ANALYZE Example Copyright @ 2025 Oracle and/or its affiliates. 40 

More with EXPLAIN It's also possible to get the QEP of a running connection using EXPLAIN FOR CONNECTION: SQL SQL > > show show processlist processlist; ; + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ | | Id Id | | User User | | Host Host | | db db | | Command Command | | Time Time | | State State | | Info Info | | + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ | | 8 8 | | root root | | localh localh | | NULL NULL | | Query Query | | 0 0 | | init init | | show show processlist processlist | | | | 9 9 | | root root | | localh localh | | test test | | Query Query | | 7 7 | | User User sleep sleep | | select select * *, , SLEEP SLEEP( (10 10) ) from from foo foo where where id id> >3 3 | | + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ 3 3 rows rows in in set set, , 0 0 warning warning ( (0.0006 0.0006 sec sec) ) SQL SQL > > explain explain for for connection connection 9 9; ; + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ | | id id | | select_type select_type | | table table | | partit partit. . | | type type | | possible_keys possible_keys | | key key | | key_len key_len | | ref ref | | rows rows | | filtered filtered | | Extra Extra | | + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ | | 1 1 | | SIMPLE SIMPLE | | foo foo | | NULL NULL | | range range | | PRIMARY PRIMARY | | PRIMARY PRIMARY | | 4 4 | | NULL NULL | | 2 2 | | 100 100 | | Using Using where where | | + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ Copyright @ 2025 Oracle and/or its affiliates. 41 

More on EXPLAIN • h�ps://dev.mysql.com/doc/refman/9.2/en/explain.html • h�ps://dev.mysql.com/doc/refman/9.2/en/explain-output.html • h�ps://dev.mysql.com/doc/refman/9.2/en/explain-extended.html • h�ps://dev.mysql.com/doc/workbench/en/wb-performance-explain.html Copyright @ 2025 Oracle and/or its affiliates. 42 

Indexes �nd rows with speci�c column values quickly Copyright @ 2025 Oracle and/or its affiliates. 43 

Indexes A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and e�cient access of ordered records. (h�ps://en.wikipedia.org/wiki/Database_index) Copyright @ 2025 Oracle and/or its affiliates. 44 

MySQL supports multiple kind of indexes: • primary key / clustered index • secondary index • full-text index • spatial index Indexes in MySQL Copyright @ 2025 Oracle and/or its affiliates. 45 

• B-TREE • HASH • R-TREE (spatial data types) • Inverted Lists (Fulltext) • pre�x of a column • mutli-column • unique • covering • functional • multi-value Indexes in MySQL (2) The main types of indexes in MySQL are : and Indexes can have some "properties": Copyright @ 2025 Oracle and/or its affiliates. 46 

Clustered Indexes Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. Copyright @ 2025 Oracle and/or its affiliates. 47 

Clustered Indexes Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. Primary Keys are very important!! Copyright @ 2025 Oracle and/or its affiliates. 47 

InnoDB Primary Key Primary Keys impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2025 Oracle and/or its affiliates. 48 

Also, it's more and more common to use applications that generate complete random primary keys...that means if the Primary Key is not sequential, InnoDB will have to heavily re-balance all the pages on inserts. InnoDB Primary Key Primary Keys impact how the values are inserted and the size of the secondary indexes. A non sequential PK can lead to many random IOPS. Copyright @ 2025 Oracle and/or its affiliates. 48 

InnoDB Primary Key (2) If we compare the same load (inserts) when using an auto_increment integer as Primary Key, we can see that only the latest pages are recently touched: Generated with h�ps://github.com/jeremycole/innodb_ruby from @jeremycole Copyright @ 2025 Oracle and/or its affiliates. 49 

InnoDB Primary Key (3) It's possible to return an error if a table is created without de�ning any Primary Key: sql_require_primary_key. SQL SQL> > SET SET sql_require_primary_key sql_require_primary_key= =1 1 ; ; SQL SQL> > CREATE CREATE TABLE TABLE nopk2 nopk2 ( (i i int int not not null null, , name name varchar varchar( (20 20) )) ); ; ERROR: ERROR: 3750 3750 ( (HY000 HY000) ): Unable : Unable to to create create or or change a change a table table without a without a primary primary key key, , when when the system variable the system variable 'sql_require_primary_key' 'sql_require_primary_key' is is set set. . Add Add a a primary primary key key to to the the table table or or unset this variable unset this variable to to avoid this message avoid this message. . Note that Note that tables tables without a without a primary primary key key can cause performance problems can cause performance problems in in row row- -based based replication replication, , so please consult your DBA before changing this setting so please consult your DBA before changing this setting. . Copyright @ 2025 Oracle and/or its affiliates. 50 

Invisible column as Primary Key - automatic Since MySQL 8.0.30 you can also enable GIPK mode ! Copyright @ 2025 Oracle and/or its affiliates. 51 

Invisible column as Primary Key - automatic Since MySQL 8.0.30 you can also enable GIPK mode ! Generated Invisible Primary Key Copyright @ 2025 Oracle and/or its affiliates. 51 

Invisible column as Primary Key - automatic Since MySQL 8.0.30 you can also enable GIPK mode ! Generated Invisible Primary Key GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. When MySQL is running in GIPK mode, a primary key is added to a table by the server, the column and key name is always my_row_id. Copyright @ 2025 Oracle and/or its affiliates. 51 

GIPK Mode - example SQL SQL > > SELECT SELECT @ @@sql_generate_invisible_primary_key @sql_generate_invisible_primary_key; ; + +--------------------------------------+ --------------------------------------+ | | @ @@sql_generate_invisible_primary_key @sql_generate_invisible_primary_key | | + +--------------------------------------+ --------------------------------------+ | | 1 1 | | + +--------------------------------------+ --------------------------------------+ SQL SQL > > CREATE CREATE TABLE TABLE sweden_mug sweden_mug ( (name name varchar varchar( (20 20) ), , beers beers int int unsigned unsigned) ); ; SQL SQL > > INSERT INSERT INTO INTO sweden_mug sweden_mug VALUES VALUES ( ('Ted' 'Ted', , 5 5) ), , ( ('lefred' 'lefred', ,1 1) ); ; SQL SQL > > SELECT SELECT * * FROM FROM sweden_mug sweden_mug; ; + +--------+-------+ --------+-------+ | | name name | | beers beers | | + +--------+-------+ --------+-------+ | | Ted Ted | | 5 5 | | | | lefred lefred | | 1 1 | | + +--------+-------+ --------+-------+ 2 2 rows rows in in set set ( (0.0002 0.0002 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 52 

GIPK Mode - example (2) SQL SQL > > SHOW SHOW CREATE CREATE TABLE TABLE sweden_mug\G sweden_mug\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: sweden_mug : sweden_mug Create Create Table Table: : CREATE CREATE TABLE TABLE ` `sweden_mug sweden_mug` ` ( ( ` `my_row_id my_row_id` ` bigint bigint unsigned unsigned NOT NOT NULL NULL AUTO_INCREMENT AUTO_INCREMENT /*!80023 INVISIBLE */ /*!80023 INVISIBLE */, , ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `beers beers` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `my_row_id my_row_id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB AUTO_INCREMENT AUTO_INCREMENT= =3 3 DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci Copyright @ 2025 Oracle and/or its affiliates. 53 

GIPK Mode - example (2) SQL SQL > > SHOW SHOW CREATE CREATE TABLE TABLE sweden_mug\G sweden_mug\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: sweden_mug : sweden_mug Create Create Table Table: : CREATE CREATE TABLE TABLE ` `sweden_mug sweden_mug` ` ( ( ` `my_row_id my_row_id` ` bigint bigint unsigned unsigned NOT NOT NULL NULL AUTO_INCREMENT AUTO_INCREMENT /*!80023 INVISIBLE */ /*!80023 INVISIBLE */, , ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `beers beers` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `my_row_id my_row_id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB AUTO_INCREMENT AUTO_INCREMENT= =3 3 DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci SQL SQL > > SELECT SELECT * *, , my_row_id my_row_id FROM FROM sweden_mug sweden_mug; ; + +--------+-------+-----------+ --------+-------+-----------+ | | name name | | beers beers | | my_row_id my_row_id | | + +--------+-------+-----------+ --------+-------+-----------+ | | ted ted | | 5 5 | | 1 1 | | | | lefred lefred | | 1 1 | | 2 2 | | + +--------+-------+-----------+ --------+-------+-----------+ Copyright @ 2025 Oracle and/or its affiliates. 53 

GIPK Mode - example (3) The information is also part of Information_Schema: SQL SQL > > SELECT SELECT COLUMN_NAME COLUMN_NAME, , ORDINAL_POSITION ORDINAL_POSITION, , DATA_TYPE DATA_TYPE, , COLUMN_KEY COLUMN_KEY FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .COLUMNS COLUMNS WHERE WHERE TABLE_NAME TABLE_NAME = = "sweden_mug" "sweden_mug"; ; + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ | | COLUMN_NAME COLUMN_NAME | | ORDINAL_POSITION ORDINAL_POSITION | | DATA_TYPE DATA_TYPE | | COLUMN_KEY COLUMN_KEY | | + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ | | beers beers | | 3 3 | | int int | | | | | | my_row_id my_row_id | | 1 1 | | bigint bigint | | PRI PRI | | | | name name | | 2 2 | | varchar varchar | | | | + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 54 

GIPK Mode - example (4) < > Copyright @ 2025 Oracle and/or its affiliates. Oh nice ! But I have this legacy application that breaks if there is an extra column part of information_schema. It seems to use it to generate forms! 55 

GIPK Mode - example (4) < > Copyright @ 2025 Oracle and/or its affiliates. Oh nice ! But I have this legacy application that breaks if there is an extra column part of information_schema. It seems to use it to generate forms! No worry, you can use one the variable with the longest name (a part of some pfs related ones): show_gipk_in_create_table_and_information_schema 55 

GIPK Mode - example (5) SQL SQL > > SET SET show_gipk_in_create_table_and_information_schema show_gipk_in_create_table_and_information_schema = = 0 0; ; SQL SQL > > SELECT SELECT COLUMN_NAME COLUMN_NAME, , ORDINAL_POSITION ORDINAL_POSITION, , DATA_TYPE DATA_TYPE, , COLUMN_KEY COLUMN_KEY FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .COLUMNS COLUMNS WHERE WHERE TABLE_NAME TABLE_NAME = = "sweden_mug" "sweden_mug"; ; + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ | | COLUMN_NAME COLUMN_NAME | | ORDINAL_POSITION ORDINAL_POSITION | | DATA_TYPE DATA_TYPE | | COLUMN_KEY COLUMN_KEY | | + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ | | beers beers | | 3 3 | | int int | | | | | | name name | | 2 2 | | varchar varchar | | | | + +-------------+------------------+-----------+------------+ -------------+------------------+-----------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 56 

GIPK Mode - example (6) SQL SQL> > select select @ @@show_gipk_in_create_table_and_information_schema @show_gipk_in_create_table_and_information_schema; ; + +----------------------------------------------------+ ----------------------------------------------------+ | | @ @@show_gipk_in_create_table_and_information_schema @show_gipk_in_create_table_and_information_schema | | + +----------------------------------------------------+ ----------------------------------------------------+ | | 0 0 | | + +----------------------------------------------------+ ----------------------------------------------------+ SQL SQL > > SHOW SHOW CREATE CREATE TABLE TABLE sweden_mug\G sweden_mug\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: sweden_mug : sweden_mug Create Create Table Table: : CREATE CREATE TABLE TABLE ` `sweden_mug sweden_mug` ` ( ( ` `name name` ` varchar varchar( (20 20) ) DEFAULT DEFAULT NULL NULL, , ` `beers beers` ` int int unsigned unsigned DEFAULT DEFAULT NULL NULL ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci Copyright @ 2025 Oracle and/or its affiliates. 57 

MySQL InnoDB Primary Keys What about UUIDs ? Copyright @ 2025 Oracle and/or its affiliates. 58 

�. generally they are completely random and cause clustered index re-banlancing �. they are included in each secondary indexes (consuming disk and memory) InnoDB Primary Key - What about UUID ? There are 2 major problems with UUID's as Primary Key: Copyright @ 2025 Oracle and/or its affiliates. 59 

InnoDB Primary Key - What about UUID ? (2) Example: SQL> CREATE TABLE smug ( uuid VARCHAR(36) DEFAULT (UUID()) PRIMARY KEY, name VARCHAR(20), beers int unsigned); SQL> SELECT * FROM smug; +--------------------------------------+---------+-------+ | uuid | name | beers | +--------------------------------------+---------+-------+ | c15d29cb-b924-11ee-9aaa-f4a475a3749c | Ted | 0 | | c15d2dbd-b924-11ee-9aaa-f4a475a3749c | lefred | 1 | | c15d2ed4-b924-11ee-9aaa-f4a475a3749c | Staffan | 0 | | c15d2fe6-b924-11ee-9aaa-f4a475a3749c | Lenka | 1 | +--------------------------------------+---------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 60 

InnoDB Primary Key - What about UUID ? (3) Let's insert 2 new records: SQL> INSERT INTO smug (name, beers) VALUES ("Carsten",1), ("Scott",5); Query OK, 2 rows affected (0.0069 sec) Copyright @ 2025 Oracle and/or its affiliates. 61 

InnoDB Primary Key - What about UUID ? (3) Let's insert 2 new records: SQL> INSERT INTO smug (name, beers) VALUES ("Carsten",1), ("Scott",5); Query OK, 2 rows affected (0.0069 sec) SQL> SELECT * FROM smug; +--------------------------------------+---------+-------+ | uuid | name | beers | +--------------------------------------+---------+-------+ | 4d16b6ef-b925-11ee-9aaa-f4a475a3749c | Carsten | 1 | | 4d16bd15-b925-11ee-9aaa-f4a475a3749c | Scott | 5 | | c15d29cb-b924-11ee-9aaa-f4a475a3749c | Ted | 0 | | c15d2dbd-b924-11ee-9aaa-f4a475a3749c | lefred | 1 | | c15d2ed4-b924-11ee-9aaa-f4a475a3749c | Staffan | 0 | | c15d2fe6-b924-11ee-9aaa-f4a475a3749c | Lenka | 1 | +--------------------------------------+---------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 61 

This an overview of inserts into a table using random UUIDs as Primary Key: InnoDB Primary Key - What about UUID ? (4) Copyright @ 2025 Oracle and/or its affiliates. 62 

InnoDB Primary Key - What about UUID ? (5) And just for info, each entry in the Primary Key Index could take up to 146 bytes(*): SQL SQL > > EXPLAIN EXPLAIN SELECT SELECT * * FROM FROM smug smug WHERE WHERE uuid uuid= ='c15d2dbd-b924-11ee-9aaa-f4a475a3749c' 'c15d2dbd-b924-11ee-9aaa-f4a475a3749c'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: smug : smug partitions: partitions: NULL NULL type type: const : const possible_keys: possible_keys: PRIMARY PRIMARY key key: : PRIMARY PRIMARY key_len: key_len: 146 146 ref: const ref: const rows rows: : 1 1 filtered: filtered: 100 100 Extra: Extra: NULL NULL (*) worse case when using characters using 4 bytes each (uft8mb4) Copyright @ 2025 Oracle and/or its affiliates. 63 

InnoDB Primary Key - What about UUID ? (6) Recommended solution �. use a smaller datatype: BINARY(16) �. store the UUID sequentially: UUID_TO_BIN(..., swap_�ag) ◦ The time-low and time-high parts (the �rst and third groups of hexadecimal digits, respectively) are swapped. Copyright @ 2025 Oracle and/or its affiliates. 64 

InnoDB Primary Key - What about UUID ? (7) Recommended solution - example SQL> CREATE TABLE smug2 ( uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY, name VARCHAR(20), beers int unsigned); SQL> SELECT * FROM smug2; +------------------------------------+--------+-------+ | uuid | name | beers | +------------------------------------+--------+-------+ | 0x11EEB92771438C999AAAF4A475A3749C | Ted | 0 | | 0x11EEB9277143902F9AAAF4A475A3749C | lefred | 1 | +------------------------------------+--------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 65 

InnoDB Primary Key - What about UUID ? (7) Recommended solution - example SQL> CREATE TABLE smug2 ( uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY, name VARCHAR(20), beers int unsigned); SQL> SELECT * FROM smug2; +------------------------------------+--------+-------+ | uuid | name | beers | +------------------------------------+--------+-------+ | 0x11EEB92771438C999AAAF4A475A3749C | Ted | 0 | | 0x11EEB9277143902F9AAAF4A475A3749C | lefred | 1 | +------------------------------------+--------+-------+ SQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM smug2; +--------------------------------------+--------+-------+ | bin_to_uuid(uuid,1) | name | beers | +--------------------------------------+--------+-------+ | 71438c99-b927-11ee-9aaa-f4a475a3749c | Ted | 0 | | 7143902f-b927-11ee-9aaa-f4a475a3749c | lefred | 1 | +--------------------------------------+--------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 65 

InnoDB Primary Key - What about UUID ? (8) Recommended solution - example SQL> INSERT INTO smug2 (name, beers) VALUES ("Carsten",1), ("Scott",5); SQL> SELECT * FROM smug2; +------------------------------------+---------+-------+ | uuid | name | beers | +------------------------------------+---------+-------+ | 0x11EEB92771438C999AAAF4A475A3749C | Ted | 0 | | 0x11EEB9277143902F9AAAF4A475A3749C | lefred | 1 | | 0x11EEB9284D66AA559AAAF4A475A3749C | Carsten | 1 | | 0x11EEB9284D66B0439AAAF4A475A3749C | Scott | 5 | +------------------------------------+---------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 66 

InnoDB Primary Key - What about UUID ? (8) Recommended solution - example SQL> INSERT INTO smug2 (name, beers) VALUES ("Carsten",1), ("Scott",5); SQL> SELECT * FROM smug2; +------------------------------------+---------+-------+ | uuid | name | beers | +------------------------------------+---------+-------+ | 0x11EEB92771438C999AAAF4A475A3749C | Ted | 0 | | 0x11EEB9277143902F9AAAF4A475A3749C | lefred | 1 | | 0x11EEB9284D66AA559AAAF4A475A3749C | Carsten | 1 | | 0x11EEB9284D66B0439AAAF4A475A3749C | Scott | 5 | +------------------------------------+---------+-------+ SQL> SELECT BIN_TO_UUID(uuid,1), name, beers FROM smug2; +--------------------------------------+---------+-------+ | bin_to_uuid(uuid,1) | name | beers | +--------------------------------------+---------+-------+ | 71438c99-b927-11ee-9aaa-f4a475a3749c | Ted | 0 | | 7143902f-b927-11ee-9aaa-f4a475a3749c | lefred | 1 | | 4d66aa55-b928-11ee-9aaa-f4a475a3749c | Carsten | 1 | | 4d66b043-b928-11ee-9aaa-f4a475a3749c | Scott | 5 | +--------------------------------------+---------+-------+ Copyright @ 2025 Oracle and/or its affiliates. 66 

InnoDB Primary Key - What about UUID ? (9) Recommended solution - example Take a look at the size of each entry in the INDEX (and same amount added to each secondary index) SQL > EXPLAIN SELECT * FROM smug2 WHERE uuid=UUID_TO_BIN("7143902f-b927-11ee-9aaa-f4a475a3749c",1)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: smug2 partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 16 ref: const rows: 1 filtered: 100 Extra: NULL Copyright @ 2025 Oracle and/or its affiliates. 67 

MySQL UUID The UUIDs generated by MySQL are UUID v1 as described in RFC4122. • UUID v1 : is a universally unique identi�er that is generated using a timestamp and the MAC address of the computer on which it was generated. • UUID v4 : is a universally unique identi�er that is generated using random numbers. • UUID v7 : is a universally unique identi�er that is generated using a timestamp and random numbers. Copyright @ 2025 Oracle and/or its affiliates. 68 

MySQL UUID The UUIDs generated by MySQL are UUID v1 as described in RFC4122. • UUID v1 : is a universally unique identi�er that is generated using a timestamp and the MAC address of the computer on which it was generated. • UUID v4 : is a universally unique identi�er that is generated using random numbers. • UUID v7 : is a universally unique identi�er that is generated using a timestamp and random numbers. With UUID v4, it's not possible to generate any sequential output. Copyright @ 2025 Oracle and/or its affiliates. 68 

MySQL - More with UUID v1 Do you know you can now the exact time when your MySQL UUIDs (v1) were created ?? Copyright @ 2025 Oracle and/or its affiliates. 69 

MySQL - More with UUID v1 Do you know you can now the exact time when your MySQL UUIDs (v1) were created ?? Take a look at h�ps://github.com/lefred/mysql-component-uuid_v1: SQL> select uuid_to_timestamp('2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e') timestamp; +-------------------------+ | timestamp | +-------------------------+ | 2024-01-23 13:51:53.887 | +-------------------------+ SQL> select uuid_to_timestamp_long('2f1d6a6f-b9ee-11ee-8b9b-c8cb9e32df8e') timestamp; +------------------------------+ | timestamp | +------------------------------+ | Tue Jan 23 13:51:53 2024 CET | +------------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 69 

MySQL - extra If you would like to play with UUIDv4 and UUIDv7, take a look at these MySQL components: • h�ps://github.com/lefred/mysql-component-uuid_v4 • h�ps://github.com/lefred/mysql-component-uuid_v7 Percona Server also includes a UUID component: • h�ps://github.com/percona/percona-server/tree/8.0/components/uuid_vx_udf Copyright @ 2025 Oracle and/or its affiliates. 70 

Secondary Indexes Copyright @ 2025 Oracle and/or its affiliates. 71 

InnoDB Secondary Key • Indexes other than the clustered index are known as secondary indexes. • Remember that in InnoDB, each record in a secondary index contains the primary key columns for the row (right most), as well as the columns speci�ed for the secondary index. • InnoDB uses this primary key value to search for the row in the clustered index. • If the Primary Key is long, the secondary indexes use more space. It's advantageous to have a short Primary Key. Copyright @ 2025 Oracle and/or its affiliates. 72 

Indexing on a pre�x of a column create create index index part_of_name part_of_name on on city city ( (name name( (10 10) )) ); ; Only the �rst 10 characters are indexed in this example and this can save space/speed. Copyright @ 2025 Oracle and/or its affiliates. 73 

Indexing on a pre�x of a column (2) Let's compare the size between this pre�x index and an index using the full column: select select database_name database_name, , table_name table_name, , index_name index_name, , stat_value stat_value * * @ @@innodb_page_size @innodb_page_size as as size_in_bytes size_in_bytes from from mysql mysql. .innodb_index_stats innodb_index_stats where where stat_name stat_name = = 'size' 'size' and and database_name database_name= ='world' 'world' and and table_name table_name= ='city' 'city' and and index_name index_name like like '%name%' '%name%' order order by by size_in_bytes size_in_bytes desc desc; ; + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size_in_bytes size_in_bytes | | + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ | | world world | | city city | | name_idx name_idx | | 212992 212992 | | | | world world | | city city | | part_of_name part_of_name | | 114688 114688 | | + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ Copyright @ 2025 Oracle and/or its affiliates. 74 

Indexing on a pre�x of a column (3) Copyright @ 2025 Oracle and/or its affiliates. 75 

Indexing on a pre�x of a column (4) Copyright @ 2025 Oracle and/or its affiliates. 76 

Indexing on a pre�x of a column (4) We see that both indexes on name are candidates and the partial one got the preference. Copyright @ 2025 Oracle and/or its affiliates. 76 

Index key_len What does that 40 mean ? Copyright @ 2025 Oracle and/or its affiliates. 77 

Index key_len What does that 40 mean ? The key_len column indicates the length of the key that MySQL decided to use. Copyright @ 2025 Oracle and/or its affiliates. 77 

Index key_len (2) < > Copyright @ 2025 Oracle and/or its affiliates. Oh... Okay... but why 40 ? It doesn't make any sense, does it ? 78 

Index key_len (2) < > Copyright @ 2025 Oracle and/or its affiliates. Oh... Okay... but why 40 ? It doesn't make any sense, does it ? In fact, we indexed the first 10 characters of the 'name' column... but this uses utf8mb4 charset: 1 character is encoded in up to 4 bytes 10 x 4 bytes = 40 bytes per record in the index 78 

Multi-column Index It's also possible to index multiple columns in one single index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; Copyright @ 2025 Oracle and/or its affiliates. 79 

Multi-column Index It's also possible to index multiple columns in one single index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; This index will be work on (�rst_name, lastname) and (�rst_name) but not on (last_name). Put highest cardinality �eld �rst ! Copyright @ 2025 Oracle and/or its affiliates. 79 

Multi-column Index It's also possible to index multiple columns in one single index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; This index will be work on (�rst_name, lastname) and (�rst_name) but not on (last_name). Put highest cardinality �eld �rst ! Indexes are parsed from left to right Copyright @ 2025 Oracle and/or its affiliates. 79 

Multi-column Index Example The value of key_len allows you to determine how many parts of a multiple-part key MySQL actually uses. Copyright @ 2025 Oracle and/or its affiliates. 80 

Multi-column Index Example The value of key_len allows you to determine how many parts of a multiple-part key MySQL actually uses. Copyright @ 2025 Oracle and/or its affiliates. show show create create table table employees\G employees\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: employees : employees Create Create Table Table: : CREATE CREATE TABLE TABLE ` `employees employees` ` ( ( ` `emp_no emp_no` ` int int NOT NOT NULL NULL, , ` `birth_date birth_date` ` date date NOT NOT NULL NULL, , ` `first_name first_name` ` varchar varchar( (14 14) ) NOT NOT NULL NULL, , ` `last_name last_name` ` varchar varchar( (16 16) ) NOT NOT NULL NULL, , ` `gender gender` ` enum enum( ('M' 'M', ,'F' 'F') ) NOT NOT NULL NULL, , ` `hire_date hire_date` ` date date NOT NOT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `emp_no emp_no` `) ), , KEY KEY ` `first_last_idx first_last_idx` ` ( (` `first_name first_name` `, ,` `last_name last_name` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci select select ( (14 14* *4 4) )+ +2 2+ +( (16 16* *4 4) )+ +2 2; ; + +-------------------+ -------------------+ | | ( (14 14* *4 4) )+ +2 2+ +( (16 16* *4 4) )+ +2 2 | | ( (+ +2 2 --> VARCHAR's length is coded on 2 bytes) --> VARCHAR's length is coded on 2 bytes) + +-------------------+ -------------------+ | | 124 124 | | + +-------------------+ -------------------+ 80 

Multi-column Index Example (2) Let's verify using JSONv2 FORMAT: SQL SQL > > set set explain_json_format_version explain_json_format_version= =2 2; ; SQL SQL > > explain explain format format= =json json into into @myqep @myqep select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where first_name first_name like like 'fred%' 'fred%' and and last_name last_name like like 'de%' 'de%' limit limit 10 10; ; SQL SQL > > select select json_extract json_extract( (@myqep @myqep, , "$.inputs[*].ranges[0]" "$.inputs[*].ranges[0]") ) as as index_ranges index_ranges; ; + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ | | index_ranges index_ranges | | + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ | | [ ["('fred' <= first_name <= 'fred ' AND 'de' <= last_name <= 'de ')" "('fred' <= first_name <= 'fred ' AND 'de' <= last_name <= 'de ')"] ] | | + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 81 

Multi-column Index Example (3) Copyright @ 2025 Oracle and/or its affiliates. 82 

Multi-column Index Example (3) 14 x 4 + 2 = 58 Copyright @ 2025 Oracle and/or its affiliates. 82 

Multi-column Index Example (4) Copyright @ 2025 Oracle and/or its affiliates. 83 

Multi-column Index Example (4) the left-most part of the index cannot be used --> the index is not used Copyright @ 2025 Oracle and/or its affiliates. 83 

select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' limit limit 10 10; ; select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' order order by by first_name first_name limit limit 10 10; ; Multi-column Index: challenge What do you think about these two statements: [A] none uses the index [B] the left-one uses the index [C] the right-one uses the index Copyright @ 2025 Oracle and/or its affiliates. 84 

select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' limit limit 10 10; ; select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' order order by by first_name first_name limit limit 10 10; ; Multi-column Index: challenge What do you think about these two statements: [A] none uses the index [B] the left-one uses the index [C] the right-one uses the index Copyright @ 2025 Oracle and/or its affiliates. 84 

Multi-column Index: hashing values If you need to perform search of the exact value (not using like or range) of multiple large columns, some times it could be more e�cient to use a hash function and index its result: SQL SQL > > alter alter table table employees employees add add column column hash_bin_names hash_bin_names binary binary( (16 16) ) generated always generated always as as ( (unhex unhex( (md5 md5( (concat concat( (first_name first_name, , last_name last_name) )) )) )) ) virtual virtual, , add add key key hash_bin_idx hash_bin_idx( (hash_bin_names hash_bin_names) ); ; Copyright @ 2025 Oracle and/or its affiliates. 85 

Multi-column Index: hashing values So now we have this information: SQL SQL > > select select first_name first_name, , last_name last_name, , hash_bin_names hash_bin_names from from employees employees limit limit 5 5; ; + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ | | first_name first_name | | last_name last_name | | hash_bin_names hash_bin_names | | + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ | | Georgi Georgi | | Facello Facello | | 0x87F253B6EDC462C31CE4C0B94A7759C6 0x87F253B6EDC462C31CE4C0B94A7759C6 | | | | Bezalel Bezalel | | Simmel Simmel | | 0x7E22DC40403977D056144607BB9F5FF1 0x7E22DC40403977D056144607BB9F5FF1 | | | | Parto Parto | | Bamford Bamford | | 0x5099CEEC19A0CBF02D90352570E296C6 0x5099CEEC19A0CBF02D90352570E296C6 | | | | Chirstian Chirstian | | Koblick Koblick | | 0xDC13ABBDC898223009F5E5403A2AC879 0xDC13ABBDC898223009F5E5403A2AC879 | | | | Kyoichi Kyoichi | | Maliniak Maliniak | | 0x1C1684B875701A01512340BCE6B319EF 0x1C1684B875701A01512340BCE6B319EF | | + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 86 

Multi-column Index: hashing values And now let's rewrite the query and check the QEP: SQL SQL > > explain explain select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where hash_bin_names hash_bin_names= =unhex unhex( (md5 md5( ('AamodDeville' 'AamodDeville') )) ) and and first_name first_name= ='Aamod' 'Aamod' and and last_name last_name like like 'Deville' 'Deville' order order by by first_name first_name limit limit 10 10\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: ref : ref possible_keys: first_last_idx possible_keys: first_last_idx, ,hash_bin_idx hash_bin_idx key key: hash_bin_idx : hash_bin_idx key_len: key_len: 17 17 ref: const ref: const rows rows: : 1 1 filtered: filtered: 5 5 Extra: Extra: Using Using where where Copyright @ 2025 Oracle and/or its affiliates. 87 

Functional Indexes MySQL supports functional key parts that index expression values rather than column or column pre�x values. Use of functional key parts enables indexing of values not stored directly in the table. Copyright @ 2025 Oracle and/or its affiliates. 88 

Functional Indexes MySQL supports functional key parts that index expression values rather than column or column pre�x values. Use of functional key parts enables indexing of values not stored directly in the table. Let's suppose we want to retrieve all employees that were hired in March: SQL SQL > > select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3; ; Copyright @ 2025 Oracle and/or its affiliates. 88 

Functional Indexes (2) Get the Query Execution Plan: SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: : ALL ALL possible_keys: possible_keys: NULL NULL key key: : NULL NULL key_len: key_len: NULL NULL ref: ref: NULL NULL rows rows: : 299379 299379 filtered: filtered: 100 100 Extra: Extra: Using Using where where Copyright @ 2025 Oracle and/or its affiliates. 89 

Functional Indexes (2) Get the Query Execution Plan: SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: : ALL ALL possible_keys: possible_keys: NULL NULL key key: : NULL NULL key_len: key_len: NULL NULL ref: ref: NULL NULL rows rows: : 299379 299379 filtered: filtered: 100 100 Extra: Extra: Using Using where where FULL TABLE SCAN ! Copyright @ 2025 Oracle and/or its affiliates. 89 

Functional Indexes (3) SQL SQL > > create create index index month_hire_idx month_hire_idx on on employees employees ( (( (month month( (hire_date hire_date) )) )) ); ; please mind the please mind the ( (( (. .. .. .) )) ) notation notation Copyright @ 2025 Oracle and/or its affiliates. 90 

Functional Indexes (3) SQL SQL > > create create index index month_hire_idx month_hire_idx on on employees employees ( (( (month month( (hire_date hire_date) )) )) ); ; please mind the please mind the ( (( (. .. .. .) )) ) notation notation SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: ref : ref possible_keys: month_hire_idx possible_keys: month_hire_idx key key: month_hire_idx : month_hire_idx key_len: key_len: 5 5 ref: const ref: const rows rows: : 51684 51684 filtered: filtered: 100 100 Extra: Extra: NULL NULL Copyright @ 2025 Oracle and/or its affiliates. 90 

Please Keep in Mind... If there is a choice between multiple indexes, MySQL normally uses the index that �nds the smallest number of rows (the most selective index). MySQL can use indexes on columns more e�ciently if they are declared as the same type and size. Copyright @ 2025 Oracle and/or its affiliates. 91 

NULL is used to designate a LACK of data: False 0 True 1 Don't know NULL NULL Indexing NULL values really drives down the performances of indexes. Copyright @ 2025 Oracle and/or its affiliates. 92 

Before Invisible Indexes �. doubt usefulness of index �. remove that index �. get phone/text/screams from power user about slow performance �. the rest of the planet seems to need that dang index ! �. recreate the index... and it can take a looooooong time After Invisible Indexes �. doubt usefulness of index �. make index invisible - optimizer can not see it! �. get phone/text/screams from power user about slow performance �. make index visible again �. blame a problem on { network | hardware | cloud | a colleague} Invisible Indexes MySQL o�ers the possibility to hide indexes from the optimizer. This feature is very useful for testing the relevance of indexes before deleting them. And very useful for the operations team. Copyright @ 2025 Oracle and/or its affiliates. 93 

How to use INVISIBLE INDEX alter alter table table employees employees alter alter index index first_last_idx invisible first_last_idx invisible; ; alter alter table table employees employees alter alter index index first_last_idx visible first_last_idx visible; ; Copyright @ 2025 Oracle and/or its affiliates. 94 

How to use INVISIBLE INDEX alter alter table table employees employees alter alter index index first_last_idx invisible first_last_idx invisible; ; alter alter table table employees employees alter alter index index first_last_idx visible first_last_idx visible; ; List all invisible indexes: select select table_name table_name, , index_name index_name, , is_visible is_visible from from information_schema information_schema. .statistics statistics where where is_visible is_visible= ='no' 'no' group group by by table_name table_name, , index_name index_name; ; + +------------+----------------+------------+ ------------+----------------+------------+ | | TABLE_NAME TABLE_NAME | | INDEX_NAME INDEX_NAME | | IS_VISIBLE IS_VISIBLE | | + +------------+----------------+------------+ ------------+----------------+------------+ | | employees employees | | first_last_idx first_last_idx | | NO NO | | + +------------+----------------+------------+ ------------+----------------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 94 

Unused Indexes Maintaining indexes that are not used has a cost as it increase the iops. Also it forces the Optimizer to work more. Using sys Schema and innodb_index_stats it's possible to identify those unused indexes: SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2025 Oracle and/or its affiliates. 95 

Unused Indexes Maintaining indexes that are not used has a cost as it increase the iops. Also it forces the Optimizer to work more. Using sys Schema and innodb_index_stats it's possible to identify those unused indexes: SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2025 Oracle and/or its affiliates. SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' and and database_name database_name= ="employees" "employees" order order by by stat_value stat_value desc desc; ; + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size size | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | employees employees | | employees employees | | hash_bin_names2 hash_bin_names2 | | 9.52 9.52 MiB MiB | | | | employees employees | | employees employees | | month_year_hire_idx month_year_hire_idx | | 6.52 6.52 MiB MiB | | | | employees employees | | dept_emp dept_emp | | dept_no dept_no | | 5.52 5.52 MiB MiB | | | | employees employees | | dept_manager dept_manager | | dept_no dept_no | | 16.00 16.00 KiB KiB | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ 4 4 rows rows in in set set ( (0.0252 0.0252 sec sec) ) 95 

Duplicate Indexes And this is the same behaviour for duplicate indexes. There is no reason to keep maintaining them: SQL SQL > > select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2025 Oracle and/or its affiliates. 96 

Duplicate Indexes And this is the same behaviour for duplicate indexes. There is no reason to keep maintaining them: SQL SQL > > select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2025 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: city table_name: city redundant_index_name: part_of_name redundant_index_name: part_of_name redundant_index_columns: Name redundant_index_columns: Name redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: name_idx dominant_index_name: name_idx dominant_index_columns: Name dominant_index_columns: Name dominant_index_non_unique: dominant_index_non_unique: 1 1 subpart_exists: subpart_exists: 1 1 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `city city` ` DROP DROP INDEX INDEX ` `part_of_name part_of_name` ` size: size: 112.00 112.00 KiB KiB * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 2. 2. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: countrylanguage table_name: countrylanguage redundant_index_name: CountryCode redundant_index_name: CountryCode redundant_index_columns: CountryCode redundant_index_columns: CountryCode redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: dominant_index_name: PRIMARY PRIMARY dominant_index_columns: CountryCode dominant_index_columns: CountryCode, ,Language Language dominant_index_non_unique: dominant_index_non_unique: 0 0 subpart_exists: subpart_exists: 0 0 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `countrylanguage countrylanguage` ` DROP DROP INDEX INDEX ` `CountryCode CountryCode` ` size: size: 64.00 64.00 KiB KiB 96 

Don't forget ! Do not take recommendations at face value, check before deleting an index. Do not delete an index immediately, but �rst set it as INVISIBLE for some time. Once in a while this index might be used, like for a monthly report. Copyright @ 2025 Oracle and/or its affiliates. 97 

Don't forget ! Do not take recommendations at face value, check before deleting an index. Do not delete an index immediately, but �rst set it as INVISIBLE for some time. Once in a while this index might be used, like for a monthly report. Copyright @ 2025 Oracle and/or its affiliates. But when I add or remove an Index, can I estimate the time left ? 97 

ALTER Progression SQL SQL > > select select stmt stmt. .thread_id thread_id, , stmt stmt. .sql_text sql_text, , stage stage. .event_name event_name as as state state, , stage stage. .work_completed work_completed, , stage stage. .work_estimated work_estimated, , lpad lpad( (concat concat( (round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, , 2 2) ), ,"%" "%") ), ,10 10, ," " " ") ) as as completed_at completed_at, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as started_ago started_ago, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ), , 10 10, , " " " ") ) as as estimated_full_time estimated_full_time, , lpad lpad( (format_pico_time format_pico_time( (( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ) - -stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as estimated_remaining_time estimated_remaining_time, , current_allocated memory current_allocated memory from from performance_schema performance_schema. .events_statements_current stmt events_statements_current stmt inner inner join join sys sys. .memory_by_thread_by_current_bytes mt memory_by_thread_by_current_bytes mt on on mt mt. .thread_id thread_id = = stmt stmt. .thread_id thread_id inner inner join join performance_schema performance_schema. .events_stages_current stage events_stages_current stage on on stage stage. .thread_id thread_id = = stmt stmt. .thread_id\G thread_id\G Copyright @ 2025 Oracle and/or its affiliates. 98 

ALTER Progression - example Copyright @ 2025 Oracle and/or its affiliates. 99 

Index Creation is slow < > Copyright @ 2025 Oracle and/or its affiliates. Creating indexes is a very slow operation even on my powerfull server with multiple cores ! Anything I can do ? 100 

Index Creation is slow < > Copyright @ 2025 Oracle and/or its affiliates. Creating indexes is a very slow operation even on my powerfull server with multiple cores ! Anything I can do ? Since MySQL 8.0.27, you have the possibility to control the maximum of parallel threads InnoDB can use to create seconday indexes ! 100 

Parallel Index Creation The amount of parallel threads used by InnoDB is controlled by innodb_ddl_threads. This new variable is coupled with another new variable: innodb_ddl_buffer_size. If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation. Copyright @ 2025 Oracle and/or its affiliates. 101 

Parallel Index Creation - example SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 102 

Parallel Index Creation - example SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) The default se�ings are: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 Copyright @ 2025 Oracle and/or its affiliates. 102 

Parallel Index Creation - example SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) The default se�ings are: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 The innodb_ddl_buffer_size is shared between all innodb_ddl_threads de�ned. If you increase the amount of threads, I recommend that you also increase the bu�er size. Copyright @ 2025 Oracle and/or its affiliates. 102 

Parallel Index Creation - example (2) To �nd the best values for these variables, let's have a look at the amount of CPU cores: SQL SQL> > select select count count from from information_schema information_schema. .INNODB_METRICS INNODB_METRICS where where name name = = 'cpu_n' 'cpu_n'; ; + +-------+ -------+ | | count count | | + +-------+ -------+ | | 16 16 | | + +-------+ -------+ We have then 16 cores to share. As my machine as plenty of memory, I will allocate 1GB for the InnoDB DDL bu�er. Copyright @ 2025 Oracle and/or its affiliates. 103 

Parallel Index Creation - example (3) SQL SQL> > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; SQL SQL> > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; SQL SQL> > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; Copyright @ 2025 Oracle and/or its affiliates. 104 

Parallel Index Creation - example (3) SQL SQL> > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; SQL SQL> > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; SQL SQL> > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; We can now retry the same index creation as previously: SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (3 3 min min 9.1862 9.1862 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 104 

Parallel Index Creation - example (4) I recommend to make tests to de�ne the optimal se�ings for your database, your hardware and data. For example, I got the best result se�ing the bu�er size to 2GB and both ddl threads and parallel read threads to 4. It took 2 min 43 sec, much be�er than the initial 9 minutes ! For more information, go to h�ps://lefred.be/content/mysql-8-0-innodb-parallel-threads- for-online-ddl-operations/ Copyright @ 2025 Oracle and/or its affiliates. 105 

Histograms help the Optimizer to take the right decision Copyright @ 2025 Oracle and/or its affiliates. 106 

Histograms What is a histogram? Wikipedia declares a histogram is an accurate representation of the distribution of numerical data. For RDBMS, a histogram is an approximation of the data distribution within a speci�c column. So in MySQL, histograms help the optimizer to �nd the most e�cient Query Plan to fetch that data. Copyright @ 2025 Oracle and/or its affiliates. 107 

Histograms in MySQL MySQL provides: • statement histograms • optimizer histograms Copyright @ 2025 Oracle and/or its affiliates. 108 

Histograms in MySQL MySQL provides: • statement histograms • optimizer histograms the second category is what we need to focus on today ! Copyright @ 2025 Oracle and/or its affiliates. 108 

Statements Histograms This is an example of query response time distribution for a statement: Copyright @ 2025 Oracle and/or its affiliates. 109 

Global Statements Histograms If you want a global overview of all statements: SELECT SELECT CONCAT CONCAT( ('<' '<', ,ROUND ROUND( (BUCKET_TIMER_HIGH BUCKET_TIMER_HIGH/ /1000000 1000000, ,2 2) ), , ' microsec (<' ' microsec (<', ,ROUND ROUND( (BUCKET_TIMER_HIGH BUCKET_TIMER_HIGH/ /1000000000 1000000000, ,2 2) ) , ,'ms)' 'ms)') ) QRT QRT, , CONCAT CONCAT( (RPAD RPAD( ('' '', ,ROUND ROUND( (BUCKET_QUANTILE BUCKET_QUANTILE* *100 100) ), ,'*' '*') ), , ROUND ROUND( (BUCKET_QUANTILE BUCKET_QUANTILE* *100 100, ,2 2) ), ,"%" "%") ) bar bar FROM FROM events_statements_histogram_global events_statements_histogram_global WHERE WHERE count_bucket count_bucket> >0 0; ; Copyright @ 2025 Oracle and/or its affiliates. 110 

Optimizer Histograms in MySQL A histogram is a distribution of data into logical buckets There are two types of histograms: • singleton • equi-height The maximum number of buckets is 1024. Copyright @ 2025 Oracle and/or its affiliates. 111 

Optimizer Histograms in MySQL - Why? Let's have a look at the query No. 90 of the TPC-DS Benchmark Suite: “What is the ratio between the number of items sold over the internet in the morning to the number of items sold in the evening of customers with a speci�ed number of dependents. Consider only websites with a high amount of content.” Copyright @ 2025 Oracle and/or its affiliates. 112 

SQL SQL> > SELECT SELECT CAST CAST( (amc amc AS AS DECIMAL DECIMAL( (15 15, , 4 4) )) ) / / CAST CAST( (pmc pmc AS AS DECIMAL DECIMAL( (15 15, , 4 4) )) ) am_pm_ratio am_pm_ratio FROM FROM ( (SELECT SELECT COUNT COUNT( (* *) ) amc amc FROM FROM web_sales web_sales, , household_demographics household_demographics, , time_dim time_dim, , web_page web_page WHERE WHERE ws_sold_time_sk ws_sold_time_sk = = time_dim time_dim. .t_time_sk t_time_sk AND AND ws_ship_hdemo_sk ws_ship_hdemo_sk = = household_demographics household_demographics. .hd_demo_sk hd_demo_sk AND AND ws_web_page_sk ws_web_page_sk = = web_page web_page. .wp_web_page_sk wp_web_page_sk AND AND time_dim time_dim. .t_hour t_hour BETWEEN BETWEEN 9 9 AND AND 9 9 + + 1 1 AND AND household_demographics household_demographics. .hd_dep_count hd_dep_count = = 2 2 AND AND web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) at at, , ( (SELECT SELECT COUNT COUNT( (* *) ) pmc pmc FROM FROM web_sales web_sales, , household_demographics household_demographics, , time_dim time_dim, , web_page web_page WHERE WHERE ws_sold_time_sk ws_sold_time_sk = = time_dim time_dim. .t_time_sk t_time_sk AND AND ws_ship_hdemo_sk ws_ship_hdemo_sk = = household_demographics household_demographics. .hd_demo_sk hd_demo_sk AND AND ws_web_page_sk ws_web_page_sk = = web_page web_page. .wp_web_page_sk wp_web_page_sk AND AND time_dim time_dim. .t_hour t_hour BETWEEN BETWEEN 15 15 AND AND 15 15 + + 1 1 AND AND household_demographics household_demographics. .hd_dep_count hd_dep_count = = 2 2 AND AND web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) pt pt ORDER ORDER BY BY am_pm_ratio am_pm_ratio LIMIT LIMIT 100 100; ; Copyright @ 2025 Oracle and/or its affiliates. 113 

Optimizer Histograms in MySQL - Why? Result and response time: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (2.3150 2.3150 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 114 

Optimizer Histograms in MySQL - Why? Now let's create an HISTOGRAM we think should help: SQL SQL> > ANALYZE ANALYZE TABLE TABLE web_page web_page UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON wp_char_count wp_char_count WITH WITH 8 8 BUCKETS BUCKETS; ; + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ | | Table Table | | Op Op | | Msg_type Msg_type | | Msg_text Msg_text | | + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ | | tpcds tpcds. .web_page web_page | | histogram histogram | | status status | | Histogram Histogram statistics statistics created created for for column column 'wp_char_count' 'wp_char_count'. . | | + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ 1 1 row row in in set set ( (0.0124 0.0124 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 115 

Optimizer Histograms in MySQL - Why? Result and response time of the same query: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (0.2549 0.2549 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 116 

Optimizer Histograms in MySQL - Why? Result and response time of the same query: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (0.2549 0.2549 sec sec) ) Almost 10x faster ! Copyright @ 2025 Oracle and/or its affiliates. 116 

Optimizer Histograms in MySQL (2) So to help the optimizer to �nd the most e�cient Query Plan, histograms can be created. As we know, a histogram is an approximation of the data distribution within a speci�c column. Histograms are useful for columns NOT being candidate to have indexes. A histogram is created or updated only on demand, so it adds no overhead when table data is modi�ed. On the other hand, the statistics become progressively more out of date when table modi�cations occur, until the next time they are updated. Copyright @ 2025 Oracle and/or its affiliates. 117 

Optimizer Histograms in MySQL - Why? Back to our previous query, why did we create a Histogram on column wp_char_count of table web_page ? Copyright @ 2025 Oracle and/or its affiliates. 118 

Optimizer Histograms in MySQL - Why? Without any statistics available, the MySQL Optimizer assumes that 11.11% of the rows in the table web_page matches "wp_char_count BETWEEN 5000 AND 5200" But in reality: SQL SQL> > SELECT SELECT ( (SELECT SELECT COUNT COUNT( (* *) ) FROM FROM web_page web_page WHERE WHERE web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) / / ( (SELECT SELECT COUNT COUNT( (* *) ) FROM FROM web_page web_page) ) AS AS ratio ratio; ; + +--------+ --------+ | | ratio ratio | | + +--------+ --------+ | | 0.0167 0.0167 | | + +--------+ --------+ Copyright @ 2025 Oracle and/or its affiliates. 119 

Optimizer Histograms - Get the estimation You can use the Optimizer Trace to know in more details the estimation done by the MySQL Optimizer : SQL SQL> > SET SET OPTIMIZER_TRACE OPTIMIZER_TRACE = = "enabled=on" "enabled=on"; ; SQL SQL> > SET SET OPTIMIZER_TRACE_MAX_MEM_SIZE OPTIMIZER_TRACE_MAX_MEM_SIZE = = 1000000 1000000; ; SQL SQL> > EXPLAIN EXPLAIN SELECT SELECT . .. .. .. . SQL SQL> > SELECT SELECT JSON_EXTRACT JSON_EXTRACT( (TRACE TRACE- -> >"$**.filtering_effect[*]" "$**.filtering_effect[*]", , "$[*]" "$[*]") ) FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .OPTIMIZER_TRACE OPTIMIZER_TRACE; ; + +-----------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------+ | | JSON_EXTRACT JSON_EXTRACT( (TRACE TRACE- -> >"$**.filtering_effect[*]" "$**.filtering_effect[*]", , "$[1]" "$[1]") ) | | + +-----------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------+ | | [ [{ {"condition" "condition": : "(`web_page`.`wp_char_count` between 5000 and 5200)" "(`web_page`.`wp_char_count` between 5000 and 5200)", , "histogram_selectivity" "histogram_selectivity": : 0.0109375 0.0109375} }] ] | | + +-----------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------+ 1 1 row row in in set set ( (0.0008 0.0008 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 120 

Two types of Histograms Equi-height: One bucket represents a range of values. This type of histograms will be created when distinct values in the column are greater than the number of buckets speci�ed in the ANALYZE TABLE syntax. Think A-G H-L M-T U-Z. Singleton: One bucket represents one single value in the column, it is the most accurate and will be created when the number of distinct values in the column is less than or equal to the number of buckets speci�ed in the ANALYZE TABLE statement. Copyright @ 2025 Oracle and/or its affiliates. 121 

Optimizer Histograms Syntax ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c2 c2, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t DROP DROP HISTOGRAM HISTOGRAM ON ON c2 c2; ; Note that the �rst statement creates three di�erent histograms on c1, c2 and c3 as an histogram is created per columns Copyright @ 2025 Oracle and/or its affiliates. 122 

Optimizer Histograms Syntax ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c2 c2, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t DROP DROP HISTOGRAM HISTOGRAM ON ON c2 c2; ; Note that the �rst statement creates three di�erent histograms on c1, c2 and c3 as an histogram is created per columns Histograms can be created for almost any data type. If a type is not supported you will get: The The column column 'doc' 'doc' has an unsupported has an unsupported data data type type. . Copyright @ 2025 Oracle and/or its affiliates. 122 

Optimizer Histograms Syntax (2) Information_Schema can be used to retrieve the info related to the created histograms: SQL SQL> > select select table_name table_name, , column_name column_name, , histogram histogram- ->> >>'$."data-type"' '$."data-type"' AS AS 'data-type' 'data-type', , histogram histogram- ->> >>'$."number-of-buckets-specified"' '$."number-of-buckets-specified"' AS AS 'specified-buckets' 'specified-buckets', , json_length json_length( (histogram histogram- ->> >>'$."buckets"' '$."buckets"') ) AS AS 'created-buckets' 'created-buckets', , histogram histogram- ->> >>'$."last-updated"' '$."last-updated"' AS AS 'last-updated' 'last-updated' from from information_schema information_schema. .column_statistics column_statistics; ; + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ | | TABLE_NAME TABLE_NAME | | COLUMN_NAME COLUMN_NAME | | data data- -type type | | specified specified- -buckets buckets | | created created- -buckets buckets | | last last- -updated updated | | + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ | | web_page web_page | | wp_char_count wp_char_count | | int int | | 8 8 | | 8 8 | | 2024 2024- -01 01- -19 19 08 08: :04 04: :46.573993 46.573993 | | | | store store | | s_gmt_offset s_gmt_offset | | decimal decimal | | 8 8 | | 1 1 | | 2024 2024- -01 01- -19 19 11 11: :20 20: :57.963225 57.963225 | | + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ 2 2 rows rows in in set set ( (0.0004 0.0004 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 123 

You can access histograms statistics It's also possible to see the values of the buckets: SQL SQL> > SELECT SELECT * * FROM FROM information_schema information_schema. .column_statistics column_statistics WHERE WHERE table_name table_name = = 'salaries' 'salaries'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * SCHEMA_NAME: employees SCHEMA_NAME: employees TABLE_NAME: salaries TABLE_NAME: salaries COLUMN_NAME: salary COLUMN_NAME: salary HISTOGRAM: { HISTOGRAM: {"buckets" "buckets": : [ [[ [38851 38851, , 43825 43825, , 0.10001877249939266 0.10001877249939266, , 4792 4792] ], , [ [43826 43826, , 48296 48296, , 0.20003202367543452 0.20003202367543452, , 4486 4486] ], , [ [48297 48297, , 52548 52548, , 0.30006459948320413 0.30006459948320413, , 4259 4259] ], , [ [52549 52549, , 56737 56737, , 0.40009441462929834 0.40009441462929834, , 4192 4192] ], , [ [56738 56738, , 61049 61049, , 0.5001159477903664 0.5001159477903664, , 4326 4326] ], , [ [61050 61050, , 65687 65687, , 0.600148523598136 0.600148523598136, , 4670 4670] ], , [ [65688 65688, , 71042 71042, , 0.7001728174208794 0.7001728174208794, , 5412 5412] ], , [ [71043 71043, , 77726 77726, , 0.8002109145519998 0.8002109145519998, , 6965 6965] ], , [ [77727 77727, , 87476 87476, , 0.9002490116831202 0.9002490116831202, , 11200 11200] ], , [ [87477 87477, , 152687 152687, , 1.0 1.0, , 37264 37264] ]] ], , "data-type" "data-type": : "int" "int", , "auto-update" "auto-update": : false false, , "null-values" "null-values": : 0.0 0.0, , "collation-id" "collation-id": : 8 8, , "last-updated" "last-updated": : "2025-03-10 11:03:52.005205" "2025-03-10 11:03:52.005205", , "sampling-rate" "sampling-rate": : 0.12582426215897724 0.12582426215897724, , "histogram-type" "histogram-type": : "equi-height" "equi-height", , "number-of-buckets-specified" "number-of-buckets-specified": : 10 10} } Copyright @ 2025 Oracle and/or its affiliates. 124 

Histogram vs Index Why you might consider a histogram instead of an index: • Maintaining an index has a cost. If you have an index, every INSERT/UPDATE/DELETE causes the index to be updated. This is not free, and will have an impact on your performance. A histogram on the other hand is created once and never updated unless you explicitly ask for it. It will thus not hurt your write performance. • If you have an index, the optimizer will need to use it to do what we call "index dives" to estimate the number of records in a given range. This might become too costly if you have for instance very long IN-lists in your query. Histogram statistics are much cheaper in this case, and might thus be more suitable. Copyright @ 2025 Oracle and/or its affiliates. 125 

Histogram vs Index - Some advice Which columns are the best candidates for histograms ? • Histograms are useful for columns that are ◦ not the �rst column of any index, and ◦ used in WHERE conditions of ▪ JOIN queries ▪ Queries with IN-subqueries ▪ ORDER BY ... LIMIT queries • Best �t for ◦ Low cardinality columns (gender, orderStatus, dayOfWeek, ENUMs) ◦ Columns with uneven distribution (skew) ◦ Stable distribution (do not change much over time) Copyright @ 2025 Oracle and/or its affiliates. 126 

Histogram vs Index - Some advice (2) • When not to create histograms: ◦ First column of an index ◦ Never used in WHERE clause ◦ Monotonically increasing column values (e.g. date columns) ◦ Histogram will need frequent updates to be accurate ◦ Consider to create index Copyright @ 2025 Oracle and/or its affiliates. 127 

MySQL HeatWave I need more performance, much more ! Copyright @ 2025 Oracle and/or its affiliates. 128 

MySQL HeatWave MySQL is also available in Oracle Cloud Infrastructure (OCI) as a managed service. It's called MySQL HeatWave. HeatWave Cluster is a massively, high performance, in-memory query accelerator for OCI MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and mixed workload. HeatWave Cluster can be enabled on demand. Copyright @ 2025 Oracle and/or its affiliates. 129 

When queries are still too slow Some times, the data is to heavy and the indexes are not manageable or don't �t in memory... in that case it's very complicate to perform query optimization. This is especially true for Analytics queries. Copyright @ 2025 Oracle and/or its affiliates. 130 

HeatWave Example - data from PiDay SQL SQL> > select select * * from from ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max hum max hum` `, , min min( (value value) ) as as ` `min hum min hum` `, , avg avg( (value value) ) as as ` `avg hum avg hum` ` from from humidity_history humidity_history group group by by device_id device_id, , day day) ) a a natural natural join join ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max temp max temp` `, , min min( (value value) ) as as ` `min temp min temp` `, , avg avg( (value value) ) as as ` `avg temp avg temp` ` from from temperature_history temperature_history group group by by device_id device_id, , day day) ) b b order order by by day day, , device_id device_id; ; + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | day day | | device_id device_id | | tot tot | | max hum max hum | | min hum min hum | | avg hum avg hum | | max max temp temp | | min min temp temp | | avg avg temp temp | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 14534 14534 | | 65.00 65.00 | | 55.00 55.00 | | 60.009273 60.009273 | | 29.99 29.99 | | 20.00 20.00 | | 22.597118 22.597118 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 31605 31605 | | 800.21 800.21 | | 1.00 1.00 | | 8.570861 8.570861 | | 814.36 814.36 | | 0.00 0.00 | | 5.079733 5.079733 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 31284 31284 | | 279.32 279.32 | | 30.00 30.00 | | 35.294440 35.294440 | | 288.44 288.44 | | 10.00 10.00 | | 12.797445 12.797445 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000001 c0000000000000001 | | 114906 114906 | | 50.00 50.00 | | 40.00 40.00 | | 45.001613 45.001613 | | 14.00 14.00 | | 9.00 9.00 | | 11.499796 11.499796 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 100913 100913 | | 65.00 65.00 | | 55.00 55.00 | | 59.999105 59.999105 | | 25.00 25.00 | | 20.00 20.00 | | 22.501319 22.501319 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 101465 101465 | | 11.00 11.00 | | 1.00 1.00 | | 5.998472 5.998472 | | 5.00 5.00 | | 0.00 0.00 | | 2.501763 2.501763 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 101044 101044 | | 40.00 40.00 | | 30.00 30.00 | | 34.991012 34.991012 | | 15.00 15.00 | | 10.00 10.00 | | 12.496505 12.496505 | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ 7 7 rows rows in in set set ( (1.2717 1.2717 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 131 

HeatWave Example - data from PiDay Same Query after having enabled and loaded data to HeatWave Cluster SQL SQL> > select select * * from from ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max hum max hum` `, , min min( (value value) ) as as ` `min hum min hum` `, , avg avg( (value value) ) as as ` `avg hum avg hum` ` from from humidity_history humidity_history group group by by device_id device_id, , day day) ) a a natural natural join join ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max temp max temp` `, , min min( (value value) ) as as ` `min temp min temp` `, , avg avg( (value value) ) as as ` `avg temp avg temp` ` from from temperature_history temperature_history group group by by device_id device_id, , day day) ) b b order order by by day day, , device_id device_id; ; + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | day day | | device_id device_id | | tot tot | | max hum max hum | | min hum min hum | | avg hum avg hum | | max max temp temp | | min min temp temp | | avg avg temp temp | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 14534 14534 | | 65.00 65.00 | | 55.00 55.00 | | 60.009272 60.009272 | | 29.99 29.99 | | 20.00 20.00 | | 22.597117 22.597117 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 31605 31605 | | 800.21 800.21 | | 1.00 1.00 | | 8.570860 8.570860 | | 814.36 814.36 | | 0.00 0.00 | | 5.079732 5.079732 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 31284 31284 | | 279.32 279.32 | | 30.00 30.00 | | 35.294440 35.294440 | | 288.44 288.44 | | 10.00 10.00 | | 12.797445 12.797445 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000001 c0000000000000001 | | 115609 115609 | | 50.00 50.00 | | 40.00 40.00 | | 45.001736 45.001736 | | 14.00 14.00 | | 9.00 9.00 | | 11.499157 11.499157 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 100913 100913 | | 65.00 65.00 | | 55.00 55.00 | | 59.999104 59.999104 | | 25.00 25.00 | | 20.00 20.00 | | 22.501318 22.501318 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 101465 101465 | | 11.00 11.00 | | 1.00 1.00 | | 5.998472 5.998472 | | 5.00 5.00 | | 0.00 0.00 | | 2.501762 2.501762 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 101044 101044 | | 40.00 40.00 | | 30.00 30.00 | | 34.991011 34.991011 | | 15.00 15.00 | | 10.00 10.00 | | 12.496504 12.496504 | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ 7 7 rows rows in in set set ( (0.1267 0.1267 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 132 

HeatWave Example - data from PiDay 0.1267 sec VS 1.2717 sec 10x faster... but that is only 1 day of data... Now lets increase the data to 14 days: +11G of data: + +----------+----------+------------+ ----------+----------+------------+ | | DATA DATA | | INDEXES INDEXES | | TOTAL SIZE TOTAL SIZE | | + +----------+----------+------------+ ----------+----------+------------+ | | 8.66 8.66 GiB GiB | | 2.93 2.93 GiB GiB | | 11.59 11.59 GiB GiB | | + +----------+----------+------------+ ----------+----------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 133 

Without HeatWave Cluster: 44 44 rows rows in in set set ( (10 10 min min 14.1022 14.1022 sec sec) ) With HeatWave Cluster: 45 45 rows rows in in set set ( (1.6051 1.6051 sec sec) ) HeatWave Example - data from PiDay 14 days of data (11GB) 69M ROWS 383x faster ! Copyright @ 2025 Oracle and/or its affiliates. 134 

HeatWave - loading data alter alter table table temperature_history secondary_engine temperature_history secondary_engine= =rapid rapid; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0257 0.0257 sec sec) ) alter alter table table temperature_history secondary_load temperature_history secondary_load; ; Query OK Query OK, , 0 0 rows rows affected affected ( (17.3070 17.3070 sec sec) ) 4.6GB of data for this table (whitout indexes) loaded to HeatWave Copyright @ 2025 Oracle and/or its affiliates. 135 

HeatWave Copyright @ 2025 Oracle and/or its affiliates. 136 

A Dark Science help the Optimizer Copyright @ 2025 Oracle and/or its affiliates. 137 

Index Hints Most of the time, the optimizer is right! But sometimes, you might know more than the optimizer and want to help it. Hints are a way to tell the optimizer how to execute a query. Copyright @ 2025 Oracle and/or its affiliates. 138 

Index Hints Most of the time, the optimizer is right! But sometimes, you might know more than the optimizer and want to help it. Hints are a way to tell the optimizer how to execute a query. Experimented MySQL DBAs might remember the following hints: • USE INDEX • FORCE INDEX • IGNORE INDEX • STRAIGHT_JOIN Copyright @ 2025 Oracle and/or its affiliates. 138 

Index Hints (2) Let's take a look at this table: CREATE CREATE TABLE TABLE ` `employees employees` ` ( ( ` `emp_no emp_no` ` int int NOT NOT NULL NULL, , ` `birth_date birth_date` ` date date NOT NOT NULL NULL, , ` `first_name first_name` ` varchar varchar( (14 14) ) NOT NOT NULL NULL, , ` `last_name last_name` ` varchar varchar( (16 16) ) NOT NOT NULL NULL, , ` `gender gender` ` enum enum( ('M' 'M', ,'F' 'F') ) NOT NOT NULL NULL, , ` `hire_date hire_date` ` date date NOT NOT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `emp_no emp_no` `) ), , KEY KEY ` `emp_no emp_no` ` ( (` `emp_no emp_no` `) ), , KEY KEY ` `first_empno_idx first_empno_idx` ` ( (` `first_name first_name` `( (3 3) ), ,` `emp_no emp_no` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci Copyright @ 2025 Oracle and/or its affiliates. 139 

Index Hints (3) Check the query and its plan: explain explain format format= =tree tree SELECT SELECT first_name first_name, , last_name last_name FROM FROM employees employees WHERE WHERE first_name first_name LIKE LIKE 'fre%' 'fre%' and and emp_no emp_no > > 10001 10001 order order by by emp_no\G emp_no\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * EXPLAIN EXPLAIN: : - -> > Sort: employees Sort: employees. .emp_no emp_no ( (cost cost= =226 226 rows rows= =502 502) ) - -> > Filter: Filter: ( (employees employees. .first_name first_name like like 'fre%' 'fre%') ) ( (cost cost= =226 226 rows rows= =502 502) ) - -> > Index Index range scan range scan on on employees employees using using first_empno_idx first_empno_idx over over ( (first_name first_name = = 'fre' 'fre' AND AND 10001 10001 < < emp_no emp_no) ), , with with index index condition: condition: ( (employees employees. .emp_no emp_no > > 10001 10001) ) ( (cost cost= =226 226 rows rows= =502 502 Copyright @ 2025 Oracle and/or its affiliates. 140 

Index Hints - USE INDEX With USE INDEX hint we can suggest the optimizer to use a speci�c index, but the optimizer can still decide to use another index or not use any index at all. explain explain format format= =tree tree SELECT SELECT first_name first_name, , last_name last_name FROM FROM employees employees USE USE INDEX INDEX ( (emp_no emp_no) ) WHERE WHERE first_name first_name like like 'fre%' 'fre%' and and emp_no emp_no > > 10001 10001 order order by by emp_no\G emp_no\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * EXPLAIN EXPLAIN: : - -> > Sort: employees Sort: employees. .emp_no emp_no ( (cost cost= =30157 30157 rows rows= =299246 299246) ) - -> > Filter: Filter: ( (( (employees employees. .first_name first_name like like 'fre%' 'fre%') ) and and ( (employees employees. .emp_no emp_no > > 10001 10001) )) ) ( (cost cost= =30157 30157 rows rows= =299246 299246) ) - -> > Table Table scan scan on on employees employees ( (cost cost= =30157 30157 rows rows= =299246 299246) ) Copyright @ 2025 Oracle and/or its affiliates. 141 

Index Hints - FORCE INDEX With FORCE INDEX hint we can force the optimizer to use a speci�c index: explain explain format format= =tree tree SELECT SELECT first_name first_name, , last_name last_name FROM FROM employees employees FORCE FORCE INDEX INDEX ( (emp_no emp_no) ) WHERE WHERE first_name first_name like like 'fre%' 'fre%' and and emp_no emp_no > > 10001 10001 order order by by emp_no\G emp_no\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * EXPLAIN EXPLAIN: : - -> > Filter: Filter: ( (employees employees. .first_name first_name like like 'fre%' 'fre%') ) ( (cost cost= =0.71 0.71 rows rows= =0.111 0.111) ) - -> > Index Index range scan range scan on on employees employees using using emp_no emp_no over over ( (10001 10001 < < emp_no emp_no) ), , with with index index condition: condition: ( (employees employees. .emp_no emp_no > > 10001 10001) ) ( (cost cost= =0.71 0.71 rows rows= =1 1) ) Copyright @ 2025 Oracle and/or its affiliates. 142 

Index Hints - IGNORE INDEX With IGNORE INDEX hint we can suggest the optimizer to ignore a speci�c index: explain explain format format= =tree tree SELECT SELECT first_name first_name, , last_name last_name FROM FROM employees employees IGNORE IGNORE INDEX INDEX ( (first_empno_idx first_empno_idx) ) WHERE WHERE first_name first_name like like 'fre%' 'fre%' and and emp_no emp_no > > 10001 10001 order order by by emp_no\G emp_no\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * EXPLAIN EXPLAIN: : - -> > Filter: Filter: ( (( (employees employees. .first_name first_name like like 'fre%' 'fre%') ) and and ( (employees employees. .emp_no emp_no > > 10001 10001) )) ) ( (cost cost= =29962 29962 rows rows= =16623 16623) ) - -> > Index Index range scan range scan on on employees employees using using PRIMARY PRIMARY over over ( (10001 10001 < < emp_no emp_no) ) ( (cost cost= =29962 29962 rows rows= =149623 149623) ) Copyright @ 2025 Oracle and/or its affiliates. 143 

Index Hints - STRAIGHT_JOIN Let's now have a look at the following query and its plan: explain explain SELECT SELECT e e. .first_name first_name, , e e. .last_name last_name, , s s. .salary salary FROM FROM salaries s salaries s JOIN JOIN employees e employees e ON ON e e. .emp_no emp_no = = s s. .emp_no emp_no WHERE WHERE e e. .emp_no emp_no = = 10001 10001 and and salary salary > > 70000 70000; ; + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ | | id id | | select_type select_type | | table table | | partitions partitions | | type type | | possible_keys possible_keys | | key key | | key_len key_len | | ref ref | | rows rows | | filtered filtered | | Extra Extra | | + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ | | 1 1 | | SIMPLE SIMPLE | | e e | | NULL NULL | | const const | | PRIMARY PRIMARY, ,emp_no emp_no | | PRIMARY PRIMARY | | 4 4 | | const const | | 1 1 | | 100 100 | | NULL NULL | | | | 1 1 | | SIMPLE SIMPLE | | s s | | NULL NULL | | ref ref | | PRIMARY PRIMARY | | PRIMARY PRIMARY | | 4 4 | | const const | | 17 17 | | 31.93 31.93 | | Using Using where where | | + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ Copyright @ 2025 Oracle and/or its affiliates. 144 

Index Hints - STRAIGHT_JOIN (2) And now let's use the STRAIGHT_JOIN hint: explain explain SELECT SELECT e e. .first_name first_name, , e e. .last_name last_name, , s s. .salary salary FROM FROM salaries s salaries s STRAIGHT_JOIN employees e STRAIGHT_JOIN employees e ON ON e e. .emp_no emp_no = = s s. .emp_no emp_no WHERE WHERE e e. .emp_no emp_no = = 10001 10001 and and salary salary > > 70000 70000; ; + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ | | id id | | select_type select_type | | table table | | partitions partitions | | type type | | possible_keys possible_keys | | key key | | key_len key_len | | ref ref | | rows rows | | filtered filtered | | Extra Extra | | + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ | | 1 1 | | SIMPLE SIMPLE | | s s | | NULL NULL | | ref ref | | PRIMARY PRIMARY | | PRIMARY PRIMARY | | 4 4 | | const const | | 17 17 | | 31.93 31.93 | | Using Using where where | | | | 1 1 | | SIMPLE SIMPLE | | e e | | NULL NULL | | const const | | PRIMARY PRIMARY, ,emp_no emp_no | | PRIMARY PRIMARY | | 4 4 | | const const | | 1 1 | | 100 100 | | NULL NULL | | + +----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ ----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+ Copyright @ 2025 Oracle and/or its affiliates. 145 

Optimizer Hints Since MySQL 8.0 we have extended the new way of comment-style hints introduced in MySQL 5.7. They allow more �ne grained control and are easier to read and write. They are able to control per query the optimizer_switch variable. See h�ps://dev.mysql.com/doc/refman/9.2/en/optimizer-hints.html Copyright @ 2025 Oracle and/or its affiliates. 146 

Optimizer Hints Since MySQL 8.0 we have extended the new way of comment-style hints introduced in MySQL 5.7. They allow more �ne grained control and are easier to read and write. They are able to control per query the optimizer_switch variable. See h�ps://dev.mysql.com/doc/refman/9.2/en/optimizer-hints.html As a DBA you can use the Query Rewrite Plugin to rewrite queries and add hints. Copyright @ 2025 Oracle and/or its affiliates. 146 

Optimizer Hints - examples Remember the previous query, now we can replace the STRAIGHT_JOIN hint with the following: SELECT SELECT /*+ JOIN_FIXED_ORDER() */ /*+ JOIN_FIXED_ORDER() */ e e. .first_name first_name, , e e. .last_name last_name, , s s. .salary salary FROM FROM salaries s salaries s JOIN JOIN employees e employees e ON ON e e. .emp_no emp_no = = s s. .emp_no emp_no WHERE WHERE e e. .emp_no emp_no = = 10001 10001 and and salary salary > > 70000 70000; ; Copyright @ 2025 Oracle and/or its affiliates. 147 

Optimizer Hints - examples (2) Another example using Batch Key Access (BKA) join: explain explain format format= =tree tree SELECT SELECT /*+ BKA(ci) */ /*+ BKA(ci) */ c c. .Name Name, , ci ci. .Name Name FROM FROM country c country c JOIN JOIN city ci city ci ON ON c c. .Code Code = = ci ci. .CountryCode CountryCode WHERE WHERE c c. .Continent Continent = = 'Asia' 'Asia'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * EXPLAIN EXPLAIN: : - -> > Batched Batched key key access access inner inner join join - -> > Batch input Batch input rows rows - -> > Filter: Filter: ( (c c. .Continent Continent = = 'Asia' 'Asia') ) ( (cost cost= =25.7 25.7 rows rows= =34.1 34.1) ) - -> > Table Table scan scan on on c c ( (cost cost= =25.7 25.7 rows rows= =239 239) ) - -> > Multi Multi- -range range index index lookup lookup on on ci ci using using CountryCode CountryCode ( (CountryCode CountryCode = = c c. .` `Code Code` `) ) ( (cost cost= =4.41 4.41 rows rows= =17.4 17.4) ) Copyright @ 2025 Oracle and/or its affiliates. 148 

Recommended Lectures books Copyright @ 2025 Oracle and/or its affiliates. 149 

Copyright @ 2025 Oracle and/or its affiliates. 150 

Share your to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2025 Oracle and/or its affiliates. 151 

Questions ? Copyright @ 2025 Oracle and/or its affiliates. 152