MySQL Indexes and Histograms

Learn about How Indexes and Histrograms can speed-up your MySQL Queries.

This session was delivered during Sweden MySQL User Group

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Sweden MySQL User Group - February 2024 MySQL Indexes and Histograms How They Really Speed Up Your Queries
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. What Is This Session About ? Copyright @ 2024 Oracle and/or its affiliates. 4
5. Speed and Performance ! Nobody ever complains that the database is too fast ! Copyright @ 2024 Oracle and/or its affiliates. 5
6. Speed and Performance ! Nobody ever complains that the database is too fast ! It's often the reverse... blame the database ! Copyright @ 2024 Oracle and/or its affiliates. 5
7. 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 @ 2024 Oracle and/or its affiliates. 5
8. 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 @ 2024 Oracle and/or its affiliates. 5
9. 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 @ 2024 Oracle and/or its affiliates. 6
10. The North Star Query Respone Time Copyright @ 2024 Oracle and/or its affiliates. 7
11. 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 @ 2024 Oracle and/or its affiliates. 8
12. Today's goal is to... reduce the Query Response Time Copyright @ 2024 Oracle and/or its affiliates. 9
13. Bad Queries Finding the Ugly Duckling Copyright @ 2024 Oracle and/or its affiliates. 10
14. What are bad queries ? We can de ne bad queries in two di erent categories: Copyright @ 2024 Oracle and/or its affiliates. 11
15. 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 @ 2024 Oracle and/or its affiliates. 11
16. What are bad queries ? Copyright @ 2024 Oracle and/or its affiliates. 12
17. 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 @ 2024 Oracle and/or its affiliates. 13
18. 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 @ 2024 Oracle and/or its affiliates. 13
19. 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 @ 2024 Oracle and/or its affiliates. 13
20. 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 @ 2024 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
21. 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 @ 2024 Oracle and/or its affiliates. 14
22. 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 @ 2024 Oracle and/or its affiliates. 14
23. 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 @ 2024 Oracle and/or its affiliates. 14
24. Copyright @ 2024 Oracle and/or its affiliates. 15
25. The Optimizer some theory Copyright @ 2024 Oracle and/or its affiliates. 16
26. 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 @ 2024 Oracle and/or its affiliates. 17
27. 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 @ 2024 Oracle and/or its affiliates. 18
28. 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 @ 2024 Oracle and/or its affiliates. 19
29. 120 if your query has ve joins the optimizer may have to evaluate 120 di erent options 5! (5 * 4 * 3 * 2 * 1) Copyright @ 2024 Oracle and/or its affiliates. 20
30. The Query Execution Plan EXPLAIN: DBAs companion Copyright @ 2024 Oracle and/or its affiliates. 21
31. 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 @ 2024 Oracle and/or its affiliates. 22
32. EXPLAIN Example Copyright @ 2024 Oracle and/or its affiliates. 23
33. EXPLAIN Example this is an ESTIMATION on how MySQL would run the query as it is not executed ! Copyright @ 2024 Oracle and/or its affiliates. 23
34. 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 @ 2024 Oracle and/or its affiliates. 24
35. 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 @ 2024 Oracle and/or its affiliates. 25
36. EXPLAIN Example (with a JOIN) Copyright @ 2024 Oracle and/or its affiliates. 26
37. Visual EXPLAIN Example from MySQL Workbench Copyright @ 2024 Oracle and/or its affiliates. 27
38. EXPLAIN FORMAT=TREE Example Copyright @ 2024 Oracle and/or its affiliates. 28
39. EXPLAIN FORMAT=TREE Example Copyright @ 2024 Oracle and/or its affiliates. 28
40. EXPLAIN FORMAT=JSON Example Copyright @ 2024 Oracle and/or its affiliates. 29
41. EXPLAIN FORMAT=JSON Example this is the most detailed estimation ! Copyright @ 2024 Oracle and/or its affiliates. 29
42. 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 @ 2024 Oracle and/or its affiliates. 30
43. 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 @ 2024 Oracle and/or its affiliates. 31
44. EXPLAIN FORMAT=JSON version 2 Copyright @ 2024 Oracle and/or its affiliates. 32
45. 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 @ 2024 Oracle and/or its affiliates. 33
46. can we know the real numbers ? Copyright @ 2024 Oracle and/or its affiliates. 34
47. 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 @ 2024 Oracle and/or its affiliates. 35
48. 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 @ 2024 Oracle and/or its affiliates. 35
49. EXPLAIN ANALYZE Example Copyright @ 2024 Oracle and/or its affiliates. 36
50. EXPLAIN ANALYZE Example Copyright @ 2024 Oracle and/or its affiliates. 37
51. EXPLAIN ANALYZE Example time is expressed in milliseconds Copyright @ 2024 Oracle and/or its affiliates. 37
52. EXPLAIN ANALYZE Example Copyright @ 2024 Oracle and/or its affiliates. 38
53. EXPLAIN ANALYZE Example Copyright @ 2024 Oracle and/or its affiliates. 39
54. EXPLAIN ANALYZE Example Copyright @ 2024 Oracle and/or its affiliates. 40
55. 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 @ 2024 Oracle and/or its affiliates. 41
56. More on EXPLAIN h ps://dev.mysql.com/doc/refman/8.3/en/explain.html h ps://dev.mysql.com/doc/refman/8.3/en/explain-output.html h ps://dev.mysql.com/doc/refman/8.3/en/explain-extended.html h ps://dev.mysql.com/doc/workbench/en/wb-performance-explain.html Copyright @ 2024 Oracle and/or its affiliates. 42
57. Indexes nd rows with speci c column values quickly Copyright @ 2024 Oracle and/or its affiliates. 43
58. 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 @ 2024 Oracle and/or its affiliates. 44
59. MySQL supports multiple kind of indexes: primary key / clustered index secondary index full-text index spatial index Indexes in MySQL Copyright @ 2024 Oracle and/or its affiliates. 45
60. 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 @ 2024 Oracle and/or its affiliates. 46
61. 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 @ 2024 Oracle and/or its affiliates. 47
62. 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. We will have a session entirely dedicated to Primary Keys after the break ! Copyright @ 2024 Oracle and/or its affiliates. 47
63. 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 @ 2024 Oracle and/or its affiliates. 48
64. 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 @ 2024 Oracle and/or its affiliates. 49
65. 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 @ 2024 Oracle and/or its affiliates. 50
66. Indexing on a pre x of a column (3) Copyright @ 2024 Oracle and/or its affiliates. 51
67. Indexing on a pre x of a column (4) Copyright @ 2024 Oracle and/or its affiliates. 52
68. 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 @ 2024 Oracle and/or its affiliates. 52
69. Index key_len What does that 40 mean ? Copyright @ 2024 Oracle and/or its affiliates. 53
70. Index key_len What does that 40 mean ? The key_len column indicates the length of the key that MySQL decided to use. Copyright @ 2024 Oracle and/or its affiliates. 53
71. Index key_len (2) < > Copyright @ 2024 Oracle and/or its affiliates. Oh... Okay... but why 40 ? It doesn't make any sense, does it ? 54
72. Index key_len (2) < > Copyright @ 2024 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 54
73. 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 @ 2024 Oracle and/or its affiliates. 55
74. 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 @ 2024 Oracle and/or its affiliates. 55
75. 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 @ 2024 Oracle and/or its affiliates. 55
76. 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 @ 2024 Oracle and/or its affiliates. 56
77. 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 @ 2024 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 | | + +-------------------+ -------------------+ 56
78. 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 @ 2024 Oracle and/or its affiliates. 57
79. Multi-column Index Example (3) Copyright @ 2024 Oracle and/or its affiliates. 58
80. Multi-column Index Example (3) 14 x 4 + 2 = 58 Copyright @ 2024 Oracle and/or its affiliates. 58
81. Multi-column Index Example (4) Copyright @ 2024 Oracle and/or its affiliates. 59
82. Multi-column Index Example (4) the left-most part of the index cannot be used --> the index is not used Copyright @ 2024 Oracle and/or its affiliates. 59
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 @ 2024 Oracle and/or its affiliates. 60
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 @ 2024 Oracle and/or its affiliates. 60
85. 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 @ 2024 Oracle and/or its affiliates. 61
86. 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 @ 2024 Oracle and/or its affiliates. 62
87. 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 @ 2024 Oracle and/or its affiliates. 63
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. Copyright @ 2024 Oracle and/or its affiliates. 64
89. 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 @ 2024 Oracle and/or its affiliates. 64
90. 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 @ 2024 Oracle and/or its affiliates. 65
91. 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 @ 2024 Oracle and/or its affiliates. 65
92. 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 @ 2024 Oracle and/or its affiliates. 66
93. 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 @ 2024 Oracle and/or its affiliates. 66
94. 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 @ 2024 Oracle and/or its affiliates. 67
95. 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 @ 2024 Oracle and/or its affiliates. 68
96. 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 @ 2024 Oracle and/or its affiliates. 69
97. 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 @ 2024 Oracle and/or its affiliates. 70
98. 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 @ 2024 Oracle and/or its affiliates. 70
99. 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 @ 2024 Oracle and/or its affiliates. 71
100. 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 @ 2024 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) ) 71
101. 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 @ 2024 Oracle and/or its affiliates. 72
102. 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 @ 2024 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 72
103. 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 @ 2024 Oracle and/or its affiliates. 73
104. 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 @ 2024 Oracle and/or its affiliates. But when I add or remove an Index, can I estimate the time left ? 73
105. 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 @ 2024 Oracle and/or its affiliates. 74
106. ALTER Progression - example Copyright @ 2024 Oracle and/or its affiliates. 75
107. Index Creation is slow < > Copyright @ 2024 Oracle and/or its affiliates. Creating indexes is a very slow operation even on my powerfull server with multiple cores ! Anything I can do ? 76
108. Index Creation is slow < > Copyright @ 2024 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 ! 76
109. 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 @ 2024 Oracle and/or its affiliates. 77
110. 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 @ 2024 Oracle and/or its affiliates. 78
111. 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 @ 2024 Oracle and/or its affiliates. 78
112. 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 @ 2024 Oracle and/or its affiliates. 78
113. 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 @ 2024 Oracle and/or its affiliates. 79
114. 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 @ 2024 Oracle and/or its affiliates. 80
115. 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 @ 2024 Oracle and/or its affiliates. 80
116. 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 @ 2024 Oracle and/or its affiliates. 81
117. Histograms help the Optimizer to take the right decision Copyright @ 2024 Oracle and/or its affiliates. 82
118. 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 @ 2024 Oracle and/or its affiliates. 83
119. Histograms in MySQL MySQL provides: statement histograms optimizer histograms Copyright @ 2024 Oracle and/or its affiliates. 84
120. Histograms in MySQL MySQL provides: statement histograms optimizer histograms the second category is what we need to focus on today ! Copyright @ 2024 Oracle and/or its affiliates. 84
121. Statements Histograms This is an example of query response time distribution for a statement: Copyright @ 2024 Oracle and/or its affiliates. 85
122. 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 @ 2024 Oracle and/or its affiliates. 86
123. 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 @ 2024 Oracle and/or its affiliates. 87
124. 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 specified number of dependents. Consider only websites with a high amount of content.” Copyright @ 2024 Oracle and/or its affiliates. 88
125. 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 @ 2024 Oracle and/or its affiliates. 89
126. 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 @ 2024 Oracle and/or its affiliates. 90
127. 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 @ 2024 Oracle and/or its affiliates. 91
128. 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 @ 2024 Oracle and/or its affiliates. 92
129. 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 @ 2024 Oracle and/or its affiliates. 92
130. 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 @ 2024 Oracle and/or its affiliates. 93
131. 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 @ 2024 Oracle and/or its affiliates. 94
132. 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 @ 2024 Oracle and/or its affiliates. 95
133. 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[*]", , "$[1]" "$[1]") ) 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 @ 2024 Oracle and/or its affiliates. 96
134. 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 @ 2024 Oracle and/or its affiliates. 97
135. 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 @ 2024 Oracle and/or its affiliates. 98
136. 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 @ 2024 Oracle and/or its affiliates. 98
137. 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 @ 2024 Oracle and/or its affiliates. 99
138. 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 @ 2024 Oracle and/or its affiliates. 100
139. 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 @ 2024 Oracle and/or its affiliates. 101
140. 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 @ 2024 Oracle and/or its affiliates. 102
141. MySQL HeatWave I need more performance, much more ! Copyright @ 2024 Oracle and/or its affiliates. 103
142. 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 @ 2024 Oracle and/or its affiliates. 104
143. 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 @ 2024 Oracle and/or its affiliates. 105
144. 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 @ 2024 Oracle and/or its affiliates. 106
145. 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 @ 2024 Oracle and/or its affiliates. 107
146. 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 @ 2024 Oracle and/or its affiliates. 108
147. 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 @ 2024 Oracle and/or its affiliates. 109
148. 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 @ 2024 Oracle and/or its affiliates. 110
149. HeatWave Copyright @ 2024 Oracle and/or its affiliates. 111
150. Recommended Lectures books Copyright @ 2024 Oracle and/or its affiliates. 112
151. Copyright @ 2024 Oracle and/or its affiliates. 113
152. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 114
153. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 115