Mastering MySQL - Tips and Best Practices for Developers

Discover how to unlock MySQL's full potential in your development projects. This talk offers practical insights, optimization strategies, and best practices for developers to write efficient queries, design robust schemas, and troubleshoot common issues. Learn how to leverage MySQL features to boost performance and ensure scalability to be ready for replication. Whether you're a beginner or a seasoned developer, this session will equip you with actionable techniques to elevate your MySQL expertise.

This session was presented in Prague and Brno to the CZJug & BrnoJug

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Czechia & Slovakia Tour - March 2025 Mastering MySQL Tips and Best Practices for Developers
2. Who am I ? about.me/lefred Copyright @ 2025 Oracle and/or its affiliates. 2
3. • @lefred • @lefredbe.bsky.social • @lefred@fosstodon.org • 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
4. Disclaimer We will use a fake test database called ecommerce for our examples. The design of the tables and the queries are not optmized on purpose. +---------------------+ | Tables_in_ecommerce | +---------------------+ | invoice_status | | order_items | | orders | | products | | reviews | | users | +---------------------+ Copyright @ 2025 Oracle and/or its affiliates. 4
5. Know your data engines, size Copyright @ 2025 Oracle and/or its affiliates. 5
6. Do not ever use CREATE TABLE... ENGINE=MyISAM anymore ! Please!! Please!! Please!! SQL SQL> > set set persist default_storage_engine persist default_storage_engine= ="InnoDB" "InnoDB"; ; SQL SQL> > set set persist_only disabled_storage_engines persist_only disabled_storage_engines= ="MyISAM" "MyISAM"; ; In MySQL 8.x, defaults are strict and InnoDB guarantees DURABILITY ! Keep your data safe ! Don't use MyISAM ! Copyright @ 2025 Oracle and/or its affiliates. 6
7. • ACID • multiple lock types • faster than MyISAM Always use InnoDB !! Copyright @ 2025 Oracle and/or its affiliates. 7
8. Know your data - storage engines Check the storage engines and the size of your tables: SQL SQL> > select select concat concat( (table_schema table_schema, , '.' '.', , table_name table_name) ) as as 'TABLE' 'TABLE', , ENGINE ENGINE, , format format( (table_rows table_rows, ,0 0) ) ` `ROWS ROWS` `, , format_bytes format_bytes( (data_length data_length) ) DATA DATA, , format_bytes format_bytes( (index_length index_length) ) IDX IDX, , format_bytes format_bytes( (data_length data_length + + index_length index_length) ) 'TOTAL SIZE' 'TOTAL SIZE' from from information_schema information_schema. .tables tables where where table_schema table_schema= ='ecommerce' 'ecommerce' order order by by data_length data_length + + index_length index_length; ; + +--------------------------+--------+--------+------------+------------+------------+ --------------------------+--------+--------+------------+------------+------------+ | | TABLE TABLE | | ENGINE ENGINE | | ROWS ROWS | | DATA DATA | | IDX IDX | | TOTAL SIZE TOTAL SIZE | | + +--------------------------+--------+--------+------------+------------+------------+ --------------------------+--------+--------+------------+------------+------------+ | | ecommerce ecommerce. .invoice_status invoice_status | | MyISAM MyISAM | | 1 1, ,557 557 | | 30.41 30.41 KiB KiB | | 1.00 1.00 KiB KiB | | 31.41 31.41 KiB KiB | | | | ecommerce ecommerce. .orders orders | | InnoDB InnoDB | | 1 1, ,259 259 | | 112.00 112.00 KiB KiB | | 80.00 80.00 KiB KiB | | 192.00 192.00 KiB KiB | | | | ecommerce ecommerce. .order_items order_items | | InnoDB InnoDB | | 6 6, ,289 289 | | 288.00 288.00 KiB KiB | | 256.00 256.00 KiB KiB | | 544.00 544.00 KiB KiB | | | | ecommerce ecommerce. .reviews reviews | | InnoDB InnoDB | | 8 8, ,955 955 | | 2.52 2.52 MiB MiB | | 800.00 800.00 KiB KiB | | 3.30 3.30 MiB MiB | | | | ecommerce ecommerce. .users users | | InnoDB InnoDB | | 3 3, ,901 901 | | 1.52 1.52 MiB MiB | | 2.34 2.34 MiB MiB | | 3.86 3.86 MiB MiB | | | | ecommerce ecommerce. .products products | | InnoDB InnoDB | | 39 39, ,744 744 | | 5.52 5.52 MiB MiB | | 0 0 bytes bytes | | 5.52 5.52 MiB MiB | | + +--------------------------+--------+--------+------------+------------+------------+ --------------------------+--------+--------+------------+------------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 8
9. Know your data - primary keys Bad primary keys can slow down your queries and your writes. It also impacts your secondary indexes and can massively grow your data size and IOPS on disk. Not having primary keys is even worse and can cause contention problems (dict_sys- >mutex) and replication lag. Copyright @ 2025 Oracle and/or its affiliates. 9
10. Know your data - primary keys Bad primary keys can slow down your queries and your writes. It also impacts your secondary indexes and can massively grow your data size and IOPS on disk. Not having primary keys is even worse and can cause contention problems (dict_sys- >mutex) and replication lag. Copyright @ 2025 Oracle and/or its affiliates. 9
11. Know your data - primary keys (2) Find tables without primary keys: SQL SQL> > select select i i. .table_id table_id, , t t. .name name from from information_schema information_schema. .innodb_indexes i innodb_indexes i join join information_schema information_schema. .innodb_tables t innodb_tables t on on ( (i i. .table_id table_id = = t t. .table_id table_id) ) where where i i. .name name= ='GEN_CLUST_INDEX' 'GEN_CLUST_INDEX' and and t t. .name name like like 'ecommerce/%' 'ecommerce/%'; ; + +----------+-------------------+ ----------+-------------------+ | | table_id table_id | | name name | | + +----------+-------------------+ ----------+-------------------+ | | 3049 3049 | | ecommerce ecommerce/ /reviews reviews | | + +----------+-------------------+ ----------+-------------------+ 1 1 row row in in set set ( (0.0053 0.0053 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 10
12. Know your data - primary keys (3) Let's verify the de�nition of this table: SQL SQL> > show show create create table table reviews\G reviews\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: reviews : reviews Create Create Table Table: : CREATE CREATE TABLE TABLE ` `reviews reviews` ` ( ( ` `user_id user_id` ` varchar varchar( (36 36) ) DEFAULT DEFAULT NULL NULL, , ` `product_id product_id` ` int int DEFAULT DEFAULT NULL NULL, , ` `rating rating` ` int int DEFAULT DEFAULT NULL NULL, , ` `review review` ` text text, , ` `created_at created_at` ` datetime datetime DEFAULT DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP, , KEY KEY ` `user_id user_id` ` ( (` `user_id user_id` `) ), , KEY KEY ` `product_id product_id` ` ( (` `product_id product_id` `) ), , CONSTRAINT CONSTRAINT ` `reviews_ibfk_1 reviews_ibfk_1` ` FOREIGN FOREIGN KEY KEY ( (` `user_id user_id` `) ) REFERENCES REFERENCES ` `users users` ` ( (` `id id` `) ), , CONSTRAINT CONSTRAINT ` `reviews_ibfk_2 reviews_ibfk_2` ` FOREIGN FOREIGN KEY KEY ( (` `product_id product_id` `) ) REFERENCES REFERENCES ` `products products` ` ( (` `id id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci 1 1 row row in in set set ( (0.0024 0.0024 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 11
13. Know your data - primary keys (4) Let's create a new identical table but �rst we will enable the generation of an invisible primary key when none is provided: SQL SQL> > set set persist sql_generate_invisible_primary_key persist sql_generate_invisible_primary_key= =1 1; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0005 0.0005 sec sec) ) SQL SQL> > CREATE CREATE TABLE TABLE ` `reviews2 reviews2` ` ( ( ` `user_id user_id` ` varchar varchar( (36 36) ) DEFAULT DEFAULT NULL NULL, , ` `product_id product_id` ` int int DEFAULT DEFAULT NULL NULL, , ` `rating rating` ` int int DEFAULT DEFAULT NULL NULL, , ` `review review` ` text text, , ` `created_at created_at` ` datetime datetime DEFAULT DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP, , KEY KEY ` `user_id user_id` ` ( (` `user_id user_id` `) ), , KEY KEY ` `product_id product_id` ` ( (` `product_id product_id` `) ), , CONSTRAINT CONSTRAINT ` `reviews2_ibfk_1 reviews2_ibfk_1` ` FOREIGN FOREIGN KEY KEY ( (` `user_id user_id` `) ) REFERENCES REFERENCES ` `users users` ` ( (` `id id` `) ), , CONSTRAINT CONSTRAINT ` `reviews2_ibfk_2 reviews2_ibfk_2` ` FOREIGN FOREIGN KEY KEY ( (` `product_id product_id` `) ) REFERENCES REFERENCES ` `products products` ` ( (` `id id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0617 0.0617 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 12
14. Know your data - primary keys (5) Let's verify: SQL SQL> > desc desc reviews2 reviews2; ; + +------------+-----------------+------+-----+-------------------+--------------------------+ ------------+-----------------+------+-----+-------------------+--------------------------+ | | Field Field | | Type Type | | Null Null | | Key Key | | Default Default | | Extra Extra | | + +------------+-----------------+------+-----+-------------------+--------------------------+ ------------+-----------------+------+-----+-------------------+--------------------------+ | | my_row_id my_row_id | | bigint bigint unsigned unsigned | | NO NO | | PRI PRI | | NULL NULL | | auto_increment auto_increment INVISIBLE INVISIBLE | | | | user_id user_id | | varchar varchar( (36 36) ) | | YES YES | | MUL MUL | | NULL NULL | | | | | | product_id product_id | | int int | | YES YES | | MUL MUL | | NULL NULL | | | | | | rating rating | | int int | | YES YES | | | | NULL NULL | | | | | | review review | | text text | | YES YES | | | | NULL NULL | | | | | | created_at created_at | | datetime datetime | | YES YES | | | | CURRENT_TIMESTAMP CURRENT_TIMESTAMP | | DEFAULT_GENERATED DEFAULT_GENERATED | | + +------------+-----------------+------+-----+-------------------+--------------------------+ ------------+-----------------+------+-----+-------------------+--------------------------+ 6 6 rows rows in in set set ( (0.0018 0.0018 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 13
15. Know your data - primary keys (6) And we can now copy the data and swap the tables: SQL SQL> > insert insert into into reviews2 reviews2 select select * * from from reviews reviews; ; Query OK Query OK, , 8991 8991 rows rows affected affected ( (0.4290 0.4290 sec sec) ) SQL SQL> > drop drop table table reviews reviews; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0269 0.0269 sec sec) ) SQL SQL> > rename rename table table reviews2 reviews2 to to reviews reviews; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0311 0.0311 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 14
16. Know your data - primary keys (7) Let's query the table: SQL SQL> > select select r r. .* * from from reviews r reviews r limit limit 1 1\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * user_id: b5f2bb5b user_id: b5f2bb5b- -f9a1 f9a1- -11 11ef ef- -8422 8422- -5 5e8693515ddb e8693515ddb product_id: product_id: 6735 6735 rating: rating: 4 4 review: Somebody mention deep Republican animal sister review: Somebody mention deep Republican animal sister . .. .. . created_at: created_at: 2025 2025- -03 03- -05 05 10 10: :22 22: :37 37 Copyright @ 2025 Oracle and/or its affiliates. 15
17. Know your data - primary keys (8) And this time if we specify my_row_id in the query: SQL SQL> > select select my_row_id my_row_id, , r r. .* * from from reviews r reviews r limit limit 1 1\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * my_row_id: my_row_id: 1 1 user_id: b5f2bb5b user_id: b5f2bb5b- -f9a1 f9a1- -11 11ef ef- -8422 8422- -5 5e8693515ddb e8693515ddb product_id: product_id: 6735 6735 rating: rating: 4 4 review: Somebody mention deep Republican animal sister review: Somebody mention deep Republican animal sister . .. .. . created_at: created_at: 2025 2025- -03 03- -05 05 10 10: :22 22: :37 37 Copyright @ 2025 Oracle and/or its affiliates. 16
18. Know your data - primary keys (3) Find tables with an eventual bad primary key: SQL SQL> > select select a a. .TABLE_SCHEMA TABLE_SCHEMA, ,a a. .TABLE_NAME TABLE_NAME, , b b. .ENGINE ENGINE, , a a. .COLUMN_NAME COLUMN_NAME, , a a. .DATA_TYPE DATA_TYPE, , a a. .COLUMN_TYPE COLUMN_TYPE, , a a. .COLUMN_KEY COLUMN_KEY, , b b. .TABLE_ROWS TABLE_ROWS from from information_schema information_schema. .COLUMNS COLUMNS as as a a join join information_schema information_schema. .TABLES TABLES as as b b using using ( (table_name table_name, ,table_schema table_schema) ) where where COLUMN_KEY COLUMN_KEY= ='PRI' 'PRI' and and ENGINE ENGINE= ="InnoDB" "InnoDB" and and DATA_TYPE DATA_TYPE not not like like '%int' '%int' and and DATA_TYPE DATA_TYPE not not like like 'enum%' 'enum%' and and DATA_TYPE DATA_TYPE not not like like 'date%' 'date%' and and DATA_TYPE DATA_TYPE not not like like 'time%' 'time%' and and a a. .table_schema table_schema= ='ecommerce' 'ecommerce'; ; + +--------------+------------+--------+-------------+-----------+-------------+------------+------------+ --------------+------------+--------+-------------+-----------+-------------+------------+------------+ | | TABLE_SCHEMA TABLE_SCHEMA | | TABLE_NAME TABLE_NAME | | ENGINE ENGINE | | COLUMN_NAME COLUMN_NAME | | DATA_TYPE DATA_TYPE | | COLUMN_TYPE COLUMN_TYPE | | COLUMN_KEY COLUMN_KEY | | TABLE_ROWS TABLE_ROWS | | + +--------------+------------+--------+-------------+-----------+-------------+------------+------------+ --------------+------------+--------+-------------+-----------+-------------+------------+------------+ | | ecommerce ecommerce | | users users | | InnoDB InnoDB | | id id | | varchar varchar | | varchar varchar( (36 36) ) | | PRI PRI | | 3901 3901 | | + +--------------+------------+--------+-------------+-----------+-------------+------------+------------+ --------------+------------+--------+-------------+-----------+-------------+------------+------------+ 1 1 row row in in set set ( (0.0012 0.0012 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 17
19. Know your data - primary keys (4) SQL SQL> > show show create create table table ecommerce ecommerce. .users\G users\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: users : users Create Create Table Table: : CREATE CREATE TABLE TABLE ` `users users` ` ( ( ` `id id` ` varchar varchar( (36 36) ) NOT NOT NULL NULL, , ` `name name` ` text text, , ` `email email` ` text text, , ` `address address` ` varchar varchar( (200 200) ) DEFAULT DEFAULT NULL NULL, , ` `city city` ` varchar varchar( (100 100) ) DEFAULT DEFAULT NULL NULL, , ` `country country` ` varchar varchar( (100 100) ) DEFAULT DEFAULT NULL NULL, , ` `created_at created_at` ` datetime datetime DEFAULT DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP, , PRIMARY PRIMARY KEY KEY ( (` `id id` `) ), , KEY KEY ` `name_idx name_idx` ` ( (` `name name` `( (100 100) )) ), , KEY KEY ` `address_idx address_idx` ` ( (` `address address` `) ), , KEY KEY ` `city_idx city_idx` ` ( (` `city city` `) ), , KEY KEY ` `country_idx country_idx` ` ( (` `country country` `) ), , KEY KEY ` `city_country_idx city_country_idx` ` ( (` `city city` `, ,` `country country` `) ), , KEY KEY ` `name_email_idx name_email_idx` ` ( (` `name name` `( (100 100) ), ,` `email email` `( (100 100) )) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci 1 1 row row in in set set ( (0.0024 0.0024 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 18
20. Know your data - primary keys (5) SQL SQL> > select select id id, , name name, , created_at created_at from from users users limit limit 5 5; ; + +--------------------------------------+-----------------+---------------------+ --------------------------------------+-----------------+---------------------+ | | id id | | name name | | created_at created_at | | + +--------------------------------------+-----------------+---------------------+ --------------------------------------+-----------------+---------------------+ | | 02 02ecfa36 ecfa36- -f3b0 f3b0- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Katie Travis Katie Travis | | 2025 2025- -02 02- -25 25 20 20: :37 37: :56 56 | | | | 02 02ed1c92 ed1c92- -f3b0 f3b0- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | James Henderson James Henderson | | 2025 2025- -02 02- -25 25 20 20: :37 37: :56 56 | | | | 02 02ed383e ed383e- -f3b0 f3b0- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | William Mercer William Mercer | | 2025 2025- -02 02- -25 25 20 20: :37 37: :56 56 | | | | 02 02ed59b2 ed59b2- -f3b0 f3b0- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Carla Manning Carla Manning | | 2025 2025- -02 02- -25 25 20 20: :37 37: :56 56 | | | | 02 02ed721d ed721d- -f3b0 f3b0- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Benjamin Ashley Benjamin Ashley | | 2025 2025- -02 02- -25 25 20 20: :37 37: :56 56 | | + +--------------------------------------+-----------------+---------------------+ --------------------------------------+-----------------+---------------------+ 5 5 rows rows in in set set ( (0.0008 0.0008 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 19
21. Know your data - primary keys (6) SQL SQL> > select select id id, , name name, , created_at created_at from from users users order order by by created_at created_at limit limit 5 5; ; + +--------------------------------------+-------------------+---------------------+ --------------------------------------+-------------------+---------------------+ | | id id | | name name | | created_at created_at | | + +--------------------------------------+-------------------+---------------------+ --------------------------------------+-------------------+---------------------+ | | 8 8a028fc4 a028fc4- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Kimberly Atkinson Kimberly Atkinson | | 2025 2025- -02 02- -25 25 10 10: :26 26: :05 05 | | | | 8 8a02cb4f a02cb4f- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Betty Davis Betty Davis | | 2025 2025- -02 02- -25 25 10 10: :26 26: :05 05 | | | | 8 8a02ac38 a02ac38- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | David Rios David Rios | | 2025 2025- -02 02- -25 25 10 10: :26 26: :05 05 | | | | 8 8a02e292 a02e292- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Brooke Weber Brooke Weber | | 2025 2025- -02 02- -25 25 10 10: :26 26: :05 05 | | | | 8 8a0279ce a0279ce- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | Carlos Harris Carlos Harris | | 2025 2025- -02 02- -25 25 10 10: :26 26: :05 05 | | + +--------------------------------------+-------------------+---------------------+ --------------------------------------+-------------------+---------------------+ 5 5 rows rows in in set set ( (0.0038 0.0038 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 20
22. Know your data - primary keys (7) Store UUIDs as BINARY(16) and swap the timestamp to have incremental values: SQL SQL> > select select uuid_to_bin uuid_to_bin( ('02ecfa36-f3b0-11ef-9704-5e1b9081e705' '02ecfa36-f3b0-11ef-9704-5e1b9081e705', , 1 1) ); ; + +--------------------------------------------------------+ --------------------------------------------------------+ | | uuid_to_bin uuid_to_bin( ('02ecfa36-f3b0-11ef-9704-5e1b9081e705' '02ecfa36-f3b0-11ef-9704-5e1b9081e705', , 1 1) ) | | + +--------------------------------------------------------+ --------------------------------------------------------+ | | 0x11EFF3B002ECFA3697045E1B9081E705 0x11EFF3B002ECFA3697045E1B9081E705 | | + +--------------------------------------------------------+ --------------------------------------------------------+ SQL SQL> > select select uuid_to_bin uuid_to_bin( ('8a028fc4-f35a-11ef-9704-5e1b9081e705' '8a028fc4-f35a-11ef-9704-5e1b9081e705', , 1 1) ); ; + +--------------------------------------------------------+ --------------------------------------------------------+ | | uuid_to_bin uuid_to_bin( ('8a028fc4-f35a-11ef-9704-5e1b9081e705' '8a028fc4-f35a-11ef-9704-5e1b9081e705', , 1 1) ) | | + +--------------------------------------------------------+ --------------------------------------------------------+ | | 0x11EFF35A8A028FC497045E1B9081E705 0x11EFF35A8A028FC497045E1B9081E705 | | + +--------------------------------------------------------+ --------------------------------------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 21
23. Know your data - use the right data type Use the right data type for your columns, and don't over size them. Don't store numbers in VARCHAR, use INT, BIGINT, DECIMAL or FLOAT. Use ENUM for columns with a limited number of values. Use JSON for columns with a variable number of a�ributes and don't use TEXT to store your JSON. Try to avoid TEXT, BLOB, VARCHAR(255) and use the smallest data type possible. Copyright @ 2025 Oracle and/or its affiliates. 22
24. Know your data - use the right data type (2) SQL SQL> > select select table_name table_name, , column_name column_name, , data_type data_type, , column_type column_type, , character_maximum_length character_maximum_length from from information_schema information_schema. .columns columns where where TABLE_SCHEMA TABLE_SCHEMA= ='ecommerce' 'ecommerce' and and data_type data_type in in ( ('blob' 'blob', , 'text' 'text', , 'varchar' 'varchar') ) order order by by 5 5 desc desc; ; + +----------------+-------------+-----------+--------------+--------------------------+ ----------------+-------------+-----------+--------------+--------------------------+ | | TABLE_NAME TABLE_NAME | | COLUMN_NAME COLUMN_NAME | | DATA_TYPE DATA_TYPE | | COLUMN_TYPE COLUMN_TYPE | | CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH | | + +----------------+-------------+-----------+--------------+--------------------------+ ----------------+-------------+-----------+--------------+--------------------------+ | | products products | | description description | | text text | | text text | | 65535 65535 | | | | reviews reviews | | review review | | text text | | text text | | 65535 65535 | | | | users users | | name name | | text text | | text text | | 65535 65535 | | | | users users | | email email | | text text | | text text | | 65535 65535 | | | | products products | | name name | | varchar varchar | | varchar varchar( (255 255) ) | | 255 255 | | | | users users | | address address | | varchar varchar | | varchar varchar( (200 200) ) | | 200 200 | | | | users users | | city city | | varchar varchar | | varchar varchar( (100 100) ) | | 100 100 | | | | users users | | country country | | varchar varchar | | varchar varchar( (100 100) ) | | 100 100 | | | | orders orders | | user_id user_id | | varchar varchar | | varchar varchar( (36 36) ) | | 36 36 | | | | reviews reviews | | user_id user_id | | varchar varchar | | varchar varchar( (36 36) ) | | 36 36 | | | | users users | | id id | | varchar varchar | | varchar varchar( (36 36) ) | | 36 36 | | | | invoice_status invoice_status | | status status | | varchar varchar | | varchar varchar( (10 10) ) | | 10 10 | | + +----------------+-------------+-----------+--------------+--------------------------+ ----------------+-------------+-----------+--------------+--------------------------+ 12 12 rows rows in in set set ( (0.0009 0.0009 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 23
25. Know your data - use the right data type (3) Let's have a look at our �rst case (products.description): SQL SQL> > select select CHAR_LENGTH CHAR_LENGTH( (description description) ) from from products products order order by by 1 1 desc desc limit limit 5 5; ; + +--------------------------+ --------------------------+ | | CHAR_LENGTH CHAR_LENGTH( (description description) ) | | + +--------------------------+ --------------------------+ | | 118 118 | | | | 118 118 | | | | 117 117 | | | | 116 116 | | | | 115 115 | | + +--------------------------+ --------------------------+ 5 5 rows rows in in set set ( (0.0312 0.0312 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 24
26. Know your data - use the right data type (3) Let's have a look at our �rst case (products.description): SQL SQL> > select select CHAR_LENGTH CHAR_LENGTH( (description description) ) from from products products order order by by 1 1 desc desc limit limit 5 5; ; + +--------------------------+ --------------------------+ | | CHAR_LENGTH CHAR_LENGTH( (description description) ) | | + +--------------------------+ --------------------------+ | | 118 118 | | | | 118 118 | | | | 117 117 | | | | 116 116 | | | | 115 115 | | + +--------------------------+ --------------------------+ 5 5 rows rows in in set set ( (0.0312 0.0312 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. Let's �x this: SQL> alter table products modify description varchar(150); 24
27. Know your data - use the right data type (4) And what about invoice_status.status ? SQL SQL> > select select status status, , count count( (* *) ) from from invoice_status invoice_status group group by by status status; ; + +--------+----------+ --------+----------+ | | status status | | count count( (* *) ) | | + +--------+----------+ --------+----------+ | | sent sent | | 1258 1258 | | | | paid paid | | 299 299 | | + +--------+----------+ --------+----------+ 2 2 rows rows in in set set ( (0.0015 0.0015 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 25
28. Know your data - use the right data type (4) And what about invoice_status.status ? SQL SQL> > select select status status, , count count( (* *) ) from from invoice_status invoice_status group group by by status status; ; + +--------+----------+ --------+----------+ | | status status | | count count( (* *) ) | | + +--------+----------+ --------+----------+ | | sent sent | | 1258 1258 | | | | paid paid | | 299 299 | | + +--------+----------+ --------+----------+ 2 2 rows rows in in set set ( (0.0015 0.0015 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. We could use an ENUM: SQL> alter table invoice_status modify status ENUM('sent', 'paid'); 25
29. Recap: don't use MyISAM SQL SQL> > set set persist default_storage_engine persist default_storage_engine= ="InnoDB" "InnoDB"; ; SQL SQL> > set set persist_only disabled_storage_engines persist_only disabled_storage_engines= ="MyISAM" "MyISAM"; ; SQL SQL> > alter alter table table < <table_name table_name> > engine engine= =InnoDB InnoDB; ; Copyright @ 2025 Oracle and/or its affiliates. 26
30. Recap: use Primary Keys, always! SQL SQL> > set set persist sql_require_primary_key persist sql_require_primary_key= =1 1; ; SQL SQL> > set set persist sql_generate_invisible_primary_key persist sql_generate_invisible_primary_key= =1 1; ; And keep your primary keys as small as possible, a good choice is: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT Copyright @ 2025 Oracle and/or its affiliates. 27
31. Recap: use Primary Keys, always! SQL SQL> > set set persist sql_require_primary_key persist sql_require_primary_key= =1 1; ; SQL SQL> > set set persist sql_generate_invisible_primary_key persist sql_generate_invisible_primary_key= =1 1; ; And keep your primary keys as small as possible, a good choice is: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT SQL SQL> > select select table_name table_name, , column_name column_name, , data_type data_type, , column_type column_type, , extra extra from from information_schema information_schema. .columns columns where where TABLE_SCHEMA TABLE_SCHEMA= ='ecommerce' 'ecommerce' and and data_type data_type = ='int' 'int' and and column_type column_type not not like like '%unsigned' '%unsigned' and and column_key column_key= ='PRI' 'PRI'; ; Copyright @ 2025 Oracle and/or its affiliates. 27
32. Recap: store UUIDs as BINARY(16) and swap the timestamp to have incremental values uuid uuid BINARY BINARY( (16 16) ) DEFAULT DEFAULT ( (UUID_TO_BIN UUID_TO_BIN( (UUID UUID( () ), , 1 1) )) ) PRIMARY PRIMARY KEY KEY Copyright @ 2025 Oracle and/or its affiliates. 28
33. Recap: use the right data type and keep them as small as possible Don't create such table: CREATE CREATE TABLE TABLE ` `products products` ` ( ( ` `id id` ` VARCHAR VARCHAR( (255 255) ) NOT NOT NULL NULL, , ` `name name` ` TEXT TEXT DEFAULT DEFAULT NULL NULL, , ` `description description` ` TEXT TEXT DEFAULT DEFAULT NULL NULL, , ` `price price` ` TEXT TEXT NOT NOT NULL NULL, , ` `created_at created_at` ` datetime datetime DEFAULT DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP ) ) ENGINE ENGINE= =InnoDB InnoDB; ; Copyright @ 2025 Oracle and/or its affiliates. 29
34. Indexes are important not too much and not too few Copyright @ 2025 Oracle and/or its affiliates. 30
35. Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. Copyright @ 2025 Oracle and/or its affiliates. 31
36. Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! Copyright @ 2025 Oracle and/or its affiliates. 31
37. Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! And �nally, you may also miss some indexes causing full tables scans :-( Copyright @ 2025 Oracle and/or its affiliates. 31
38. Check your indexes It's important to not maintain unused indexes, this can slow down write operations and load the Optimizer for the QEP creation. And it's the same for duplicate indexes ! And �nally, you may also miss some indexes causing full tables scans :-( MySQL provides you useful information through sys schema. Copyright @ 2025 Oracle and/or its affiliates. 31
39. 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 sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 join join mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 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 = = 'ecommerce' 'ecommerce' order order by by stat_value stat_value desc desc; ; Copyright @ 2025 Oracle and/or its affiliates. 32
40. 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 sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 join join mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 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 = = 'ecommerce' 'ecommerce' order order by by stat_value stat_value desc desc; ; + +---------------+-------------+------------------+------------+ ---------------+-------------+------------------+------------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size size | | + +---------------+-------------+------------------+------------+ ---------------+-------------+------------------+------------+ | | ecommerce ecommerce | | users users | | address_idx address_idx | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | users users | | city_idx city_idx | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | users users | | country_idx country_idx | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | users users | | city_country_idx city_country_idx | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | users users | | name_email_idx name_email_idx | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | reviews reviews | | user_id user_id | | 1.52 1.52 MiB MiB | | | | ecommerce ecommerce | | order_items order_items | | product_id product_id | | 256.00 256.00 KiB KiB | | + +---------------+-------------+------------------+------------+ ---------------+-------------+------------------+------------+ Copyright @ 2025 Oracle and/or its affiliates. 32
41. Duplicate Indexes 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' and and database_name database_name = = 'ecommerce' 'ecommerce' order order by by stat_value stat_value desc desc\G \G Copyright @ 2025 Oracle and/or its affiliates. 33
42. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: ecommerce table_schema: ecommerce table_name: users table_name: users redundant_index_name: city_idx redundant_index_name: city_idx redundant_index_columns: city redundant_index_columns: city redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: city_country_idx dominant_index_name: city_country_idx dominant_index_columns: city dominant_index_columns: city, ,country country dominant_index_non_unique: dominant_index_non_unique: 1 1 subpart_exists: subpart_exists: 0 0 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `ecommerce ecommerce` `. .` `users users` ` DROP DROP INDEX INDEX ` `city_idx city_idx` ` size: size: 1.52 1.52 MiB MiB * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 2. 2. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: ecommerce table_schema: ecommerce table_name: users table_name: users redundant_index_name: name_idx redundant_index_name: name_idx redundant_index_columns: name redundant_index_columns: name redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: name_email_idx dominant_index_name: name_email_idx dominant_index_columns: name dominant_index_columns: name, ,email email 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 ` `ecommerce ecommerce` `. .` `users users` ` DROP DROP INDEX INDEX ` `name_idx name_idx` ` size: size: 528.00 528.00 KiB KiB Copyright @ 2025 Oracle and/or its affiliates. 34
43. Best Practices to drop indexes Instead of dropping an index, it's recommended to set it as invisible for a while and check if it's really not used. When this is con�rmed, you can drop it. SQL SQL> > alter alter table table users users alter alter index index city_idx invisible city_idx invisible; ; Copyright @ 2025 Oracle and/or its affiliates. 35
44. Best Practices to drop indexes (2) To list all invisible indexes: SQL SQL> > select select TABLE_NAME TABLE_NAME, , INDEX_NAME INDEX_NAME, , IS_VISIBLE IS_VISIBLE from from INFORMATION_SCHEMA INFORMATION_SCHEMA. .STATISTICS STATISTICS where where TABLE_SCHEMA TABLE_SCHEMA = = 'ecommerce' 'ecommerce' and and IS_VISIBLE IS_VISIBLE= ='no' 'no'; ; + +------------+------------+------------+ ------------+------------+------------+ | | TABLE_NAME TABLE_NAME | | INDEX_NAME INDEX_NAME | | IS_VISIBLE IS_VISIBLE | | + +------------+------------+------------+ ------------+------------+------------+ | | users users | | city_idx city_idx | | NO NO | | + +------------+------------+------------+ ------------+------------+------------+ 1 1 row row in in set set ( (0.0013 0.0013 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 36
45. Best Practices to drop indexes (3) You can also use SHOW INDEX FROM if you prefer: SQL SQL> > show show index index from from users users where where visible visible= ='no' 'no'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: users : users Non_unique: Non_unique: 1 1 Key_name: city_idx Key_name: city_idx Seq_in_index: Seq_in_index: 1 1 Column_name: city Column_name: city Collation: A Collation: A Cardinality: Cardinality: 963 963 Sub_part: Sub_part: NULL NULL Packed: Packed: NULL NULL Null Null: YES : YES Index_type: Index_type: BTREE BTREE Comment Comment: : Index_comment: Index_comment: Visible: Visible: NO NO Expression: Expression: NULL NULL Copyright @ 2025 Oracle and/or its affiliates. 37
46. SQL SQL> > select select * * from from sys sys. .schema_tables_with_full_table_scans schema_tables_with_full_table_scans where where object_schema object_schema= ='ecommerce' 'ecommerce'; ; + +---------------+-------------+-------------------+----------+ ---------------+-------------+-------------------+----------+ | | object_schema object_schema | | object_name object_name | | rows_full_scanned rows_full_scanned | | latency latency | | + +---------------+-------------+-------------------+----------+ ---------------+-------------+-------------------+----------+ | | ecommerce ecommerce | | products products | | 135320995 135320995 | | 1.13 1.13 min min | | | | ecommerce ecommerce | | users users | | 19139037 19139037 | | 10.25 10.25 s s | | | | ecommerce ecommerce | | order_items order_items | | 10075569 10075569 | | 2.71 2.71 s s | | | | ecommerce ecommerce | | orders orders | | 6100757 6100757 | | 1.81 1.81 s s | | + +---------------+-------------+-------------------+----------+ ---------------+-------------+-------------------+----------+ Missing Indexes Copyright @ 2025 Oracle and/or its affiliates. 38
47. Missing Indexes (2) SQL SQL > > select select t1 t1. .* *, , query_sample_text query_sample_text from from sys sys. .statements_with_full_table_scans t1 statements_with_full_table_scans t1 join join performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest using using( (digest digest) ) where where query query like like '%products%' '%products%'\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * query: query: SELECT SELECT * * FROM FROM ` `ecommerce ecommerce` ` . . ` `products products` ` WHERE WHERE ` `price price` ` > > ? ? db: db: NULL NULL exec_count: exec_count: 4816 4816 total_latency: total_latency: 1.19 1.19 min min no_index_used_count: no_index_used_count: 4816 4816 no_good_index_used_count: no_good_index_used_count: 0 0 no_index_used_pct: no_index_used_pct: 100 100 rows_sent: rows_sent: 90978218 90978218 rows_examined: rows_examined: 91909000 91909000 rows_sent_avg: rows_sent_avg: 18891 18891 rows_examined_avg: rows_examined_avg: 19084 19084 first_seen: first_seen: 2025 2025- -02 02- -24 24 20 20: :20 20: :17.480185 17.480185 last_seen: last_seen: 2025 2025- -02 02- -25 25 22 22: :01 01: :18.556123 18.556123 digest: c330311f85f465c26e40ae506313b8a02589fd3c8e59742c30dc0179e25cbd16 digest: c330311f85f465c26e40ae506313b8a02589fd3c8e59742c30dc0179e25cbd16 query_sample_text: query_sample_text: SELECT SELECT * * FROM FROM ecommerce ecommerce. .products products WHERE WHERE price price> >100 100 Copyright @ 2025 Oracle and/or its affiliates. 39
48. Best Practices to add indexes 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. 40
49. 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. 41
50. 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. 41
51. 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. 41
52. 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. 42
53. 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. 43
54. 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. 43
55. 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, on my system, 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 ! Copyright @ 2025 Oracle and/or its affiliates. 44
56. Recap: don't maintain useless indexes • remove duplicate indexes • remove unused indexes (set them invisible �rst) Copyright @ 2025 Oracle and/or its affiliates. 45
57. Recap: add missing indexes and do it fast! • check for full table scans • tune the parallel index creation 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. 46
58. Workload what are my queries? Copyright @ 2025 Oracle and/or its affiliates. 47
59. What is my workload? Many people don't really know if their workload is read or write intensive, or they think they know! Let's see how to �nd out: SQL SQL> > select select sum sum( (count_read count_read) ) ` `tot reads tot reads` `, , concat concat( (round round( (( (sum sum( (count_read count_read) )/ /sum sum( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `reads reads` `, , sum sum( (count_write count_write) ) ` `tot writes tot writes` `, , concat concat( (round round( (( (sum sum( (count_write count_write) )/ /sum sum( (count_star count_star) )) )* *100 100, , 2 2) ), ,"%" "%") ) ` `writes writes` ` from from performance_schema performance_schema. .table_io_waits_summary_by_table table_io_waits_summary_by_table where where count_star count_star > > 0 0 and and object_schema object_schema= ='ecommerce' 'ecommerce' ; ; + +------------+---------+------------+--------+ ------------+---------+------------+--------+ | | tot tot reads reads | | reads reads | | tot writes tot writes | | writes writes | | + +------------+---------+------------+--------+ ------------+---------+------------+--------+ | | 1334124400 1334124400 | | 100.00 100.00% % | | 51692 51692 | | 0.00 0.00% % | | + +------------+---------+------------+--------+ ------------+---------+------------+--------+ Copyright @ 2025 Oracle and/or its affiliates. 48
60. What is my workload? (2) And we can check by tables: SQL SQL> > select select object_schema object_schema, , object_name object_name, , concat concat( (round round( (( (count_read count_read/ /count_star count_star) )* *100 100, , 2 2) ), ,"%" "%") ) ` `reads reads` `, , concat concat( (round round( (( (count_write count_write/ /count_star count_star) )* *100 100, , 2 2) ), ,"%" "%") ) ` `writes writes` ` from from performance_schema performance_schema. .table_io_waits_summary_by_table table_io_waits_summary_by_table where where count_star count_star > > 0 0 and and object_schema object_schema= ='ecommerce' 'ecommerce' ; ; + +---------------+----------------+---------+---------+ ---------------+----------------+---------+---------+ | | object_schema object_schema | | object_name object_name | | reads reads | | writes writes | | + +---------------+----------------+---------+---------+ ---------------+----------------+---------+---------+ | | ecommerce ecommerce | | users users | | 99.99 99.99% % | | 0.01 0.01% % | | | | ecommerce ecommerce | | orders orders | | 99.96 99.96% % | | 0.04 0.04% % | | | | ecommerce ecommerce | | order_items order_items | | 99.89 99.89% % | | 0.11 0.11% % | | | | ecommerce ecommerce | | reviews reviews | | 99.97 99.97% % | | 0.03 0.03% % | | | | ecommerce ecommerce | | products products | | 100.00 100.00% % | | 0.00 0.00% % | | | | ecommerce ecommerce | | invoice_status invoice_status | | 0.00 0.00% % | | 100.00 100.00% % | | + +---------------+----------------+---------+---------+ ---------------+----------------+---------+---------+ Copyright @ 2025 Oracle and/or its affiliates. 49
61. Find the Ugly Duckling 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. 50
62. Find the Ugly Duckling 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. 50
63. Find the Ugly Duckling 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 = = 'ecommerce' 'ecommerce' 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. 50
64. Find the Ugly Duckling 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 = = 'ecommerce' 'ecommerce' 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. row *************************** schema_name: ecommerce tot_lat: 32.18 s exec_count: 199 latency_per_call: 161.72 ms query_sample_text: SELECT products.name, COUNT(reviews.rating) AS review_count FROM ecommerce.products LEFT JOIN ecommerce.reviews ON products.id = reviews.product_id GROUP BY products.name ORDER BY review_count DESC LIMIT 1 50
65. Copyright @ 2025 Oracle and/or its affiliates. 51
66. Sys Schema is you friend The sys schema is your friend, it contains all the necessary information to �nd the queries that need to be optimized. We use these 5 tables containing the necessary information: SQL SQL> > show show tables tables like like 'statements_with%' 'statements_with%'; ; + +---------------------------------------------+ ---------------------------------------------+ | | Tables_in_sys Tables_in_sys ( (statements_with statements_with% %) ) | | + +---------------------------------------------+ ---------------------------------------------+ | | statements_with_errors_or_warnings statements_with_errors_or_warnings | | | | statements_with_full_table_scans statements_with_full_table_scans | | | | statements_with_runtimes_in_95th_percentile statements_with_runtimes_in_95th_percentile | | | | statements_with_sorting statements_with_sorting | | | | statements_with_temp_tables statements_with_temp_tables | | + +---------------------------------------------+ ---------------------------------------------+ 5 5 rows rows in in set set ( (0.0011 0.0011 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 52
67. Recap: know your workload • check if your workload is read or write intensive • use sys schema to �nd the queries to optimize • QUERY_SAMPLE_TEXT contains the eventual comments, so use them! select select name name, , price price /* query 1 */ /* query 1 */ from from products products where where price price> >100 100 Copyright @ 2025 Oracle and/or its affiliates. 53
68. Query Execution Plan how to optimize my queries? Copyright @ 2025 Oracle and/or its affiliates. 54
69. Query Execution Plan The Query Execution Plan (QEP) is the roadmap that the MySQL Optimizer uses to execute your query. The QEP can be displayed using the keyword EXPLAIN. There are di�erent formats to display the QEP: • TRADITIONAL • TREE • JSON (v1 and v2) Copyright @ 2025 Oracle and/or its affiliates. 55
70. Query Execution Plan The Query Execution Plan (QEP) is the roadmap that the MySQL Optimizer uses to execute your query. The QEP can be displayed using the keyword EXPLAIN. There are di�erent formats to display the QEP: • TRADITIONAL • TREE • JSON (v1 and v2) this is an ESTIMATION on how MySQL would run the query as it is not executed ! Copyright @ 2025 Oracle and/or its affiliates. 55
71. Query Execution Plan - OUTPUT Let's see the QEP for our Ugly Duckling using di�erent formats: We use the following syntax: EXPLAIN EXPLAIN FORMAT FORMAT= =< <format format> > SELECT SELECT products products. .name name, , COUNT COUNT( (reviews reviews. .rating rating) ) AS AS review_count review_count FROM FROM ecommerce ecommerce. .products products LEFT LEFT JOIN JOIN ecommerce ecommerce. .reviews reviews ON ON products products. .id id = = reviews reviews. .product_id product_id GROUP GROUP BY BY products products. .name name ORDER ORDER BY BY review_count review_count DESC DESC LIMIT LIMIT 1 1 \G \G If we don't specify the format, the value of the variable explain_format is used. If JSON is used, the version is de�ned by the variable explain_json_format_version. Copyright @ 2025 Oracle and/or its affiliates. 56
72. Query Execution Plan - TRADITIONAL * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: products : products 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: : 79480 79480 filtered: filtered: 100 100 Extra: Extra: Using Using temporary temporary; ; Using Using filesort filesort * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 2. 2. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: reviews : reviews partitions: partitions: NULL NULL type type: ref : ref possible_keys: product_id possible_keys: product_id key key: product_id : product_id key_len: key_len: 5 5 ref: ecommerce ref: ecommerce. .products products. .id id rows rows: : 1 1 filtered: filtered: 100 100 Extra: Extra: NULL NULL Copyright @ 2025 Oracle and/or its affiliates. 57
73. Query Execution Plan - TREE EXPLAIN EXPLAIN: : - -> > Limit Limit: : 1 1 row row( (s s) ) - -> > Sort: review_count Sort: review_count DESC DESC, , limit limit input input to to 1 1 row row( (s s) ) per chunk per chunk - -> > Table Table scan scan on on < <temporary temporary> > - -> > Aggregate Aggregate using using temporary temporary table table - -> > Nested Nested loop loop left left join join ( (cost cost= =51675 51675 rows rows= =124453 124453) ) - -> > Table Table scan scan on on products products ( (cost cost= =8116 8116 rows rows= =79480 79480) ) - -> > Index Index lookup lookup on on reviews reviews using using product_id product_id ( (product_id product_id = = products products. .id id) ) ( (cost cost= =0.391 0.391 rows rows= =1.57 1.57) ) Copyright @ 2025 Oracle and/or its affiliates. 58
74. Query Execution Plan - JSON Copyright @ 2025 Oracle and/or its affiliates. 59
75. Query Execution Plan - JSONv2 Copyright @ 2025 Oracle and/or its affiliates. 60
76. Query Cost It's always nice to know the query cost of a query. This is how to obtain it using the JSON format of EXPLAIN: SQL SQL> > explain explain format format= =json json into into @qep @qep SELECT SELECT products products. .name name, , COUNT COUNT( (reviews reviews. .rating rating) ) AS AS review_count review_count FROM FROM ecommerce ecommerce. .products products LEFT LEFT JOIN JOIN ecommerce ecommerce. .reviews reviews ON ON products products. .id id = = reviews reviews. .product_id product_id GROUP GROUP BY BY products products. .name name ORDER ORDER BY BY review_count review_count DESC DESC LIMIT LIMIT 1 1; ; Copyright @ 2025 Oracle and/or its affiliates. 61
77. V1: SQL SQL> > select select json_extract json_extract( (@qep @qep, , "$**.query_cost" "$**.query_cost") ) as as query_cost query_cost; ; + +--------------+ --------------+ | | query_cost query_cost | | + +--------------+ --------------+ | | [ ["51674.79" "51674.79"] ] | | + +--------------+ --------------+ V2: SQL SQL> > select select max max( (cast cast( (value value as as decimal decimal( (20 20, ,10 10) )) )) ) as as max_query_cost max_query_cost from from json_table json_table( ( json_extract json_extract( (@qep @qep, , "$**.estimated_total_cost" "$**.estimated_total_cost") ), , "$[*]" "$[*]" columns columns ( (value value json path json path "$" "$") ) ) ) as as cost_table cost_table; ; + +------------------+ ------------------+ | | max_query_cost max_query_cost | | + +------------------+ ------------------+ | | 51674.7939157486 51674.7939157486 | | + +------------------+ ------------------+ Query Cost (2) Copyright @ 2025 Oracle and/or its affiliates. 62
78. EXPLAIN ANALYZE - real numbers With EXPLAIN ANALYZE you can get the real numbers of the query execution: Copyright @ 2025 Oracle and/or its affiliates. 63
79. EXPLAIN ANALYZE - real numbers With EXPLAIN ANALYZE you can get the real numbers of the query execution: Copyright @ 2025 Oracle and/or its affiliates. 64
80. EXPLAIN ANALYZE - real numbers With EXPLAIN ANALYZE you can get the real numbers of the query execution: Copyright @ 2025 Oracle and/or its affiliates. 65
81. EXPLAIN ANALYZE - real numbers With EXPLAIN ANALYZE you can get the real numbers of the query execution: Copyright @ 2025 Oracle and/or its affiliates. 66
82. EXPLAIN ANALYZE - real numbers With EXPLAIN ANALYZE you can get the real numbers of the query execution: Copyright @ 2025 Oracle and/or its affiliates. 67
83. Old DBA veri�cation We can verify this by using this simple test: SQL SQL> > flush flush status status; ; SQL SQL> > select select products products. .name name, , count count( (reviews reviews. .rating rating) ) as as review_count review_count from from ecommerce ecommerce. .products products left left join join ecommerce ecommerce. .reviews reviews on on products products. .id id = = reviews reviews. .product_id product_id group group by by products products. .name name order order by by review_count review_count desc desc limit limit 1 1; ; SQL SQL> > show show status status like like 'handler_read_%' 'handler_read_%'; ; + +-----------------------+--------+ -----------------------+--------+ | | Variable_name Variable_name | | Value Value | | + +-----------------------+--------+ -----------------------+--------+ | | Handler_read_first Handler_read_first | | 1 1 | | | | Handler_read_key Handler_read_key | | 168909 168909 | | | | Handler_read_last Handler_read_last | | 0 0 | | | | Handler_read_next Handler_read_next | | 27943 27943 | | | | Handler_read_prev Handler_read_prev | | 0 0 | | | | Handler_read_rnd Handler_read_rnd | | 0 0 | | | | Handler_read_rnd_next Handler_read_rnd_next | | 157082 157082 | | + +-----------------------+--------+ -----------------------+--------+ Copyright @ 2025 Oracle and/or its affiliates. 68
84. Old DBA veri�cation We can verify this by using this simple test: SQL SQL> > flush flush status status; ; SQL SQL> > select select products products. .name name, , count count( (reviews reviews. .rating rating) ) as as review_count review_count from from ecommerce ecommerce. .products products left left join join ecommerce ecommerce. .reviews reviews on on products products. .id id = = reviews reviews. .product_id product_id group group by by products products. .name name order order by by review_count review_count desc desc limit limit 1 1; ; SQL SQL> > show show status status like like 'handler_read_%' 'handler_read_%'; ; + +-----------------------+--------+ -----------------------+--------+ | | Variable_name Variable_name | | Value Value | | + +-----------------------+--------+ -----------------------+--------+ | | Handler_read_first Handler_read_first | | 1 1 | | | | Handler_read_key Handler_read_key | | 168909 168909 | | | | Handler_read_last Handler_read_last | | 0 0 | | | | Handler_read_next Handler_read_next | | 27943 27943 | | | | Handler_read_prev Handler_read_prev | | 0 0 | | | | Handler_read_rnd Handler_read_rnd | | 0 0 | | | | Handler_read_rnd_next Handler_read_rnd_next | | 157082 157082 | | + +-----------------------+--------+ -----------------------+--------+ Copyright @ 2025 Oracle and/or its affiliates. SQL> select 77080+80000; +-------------+ | 77080+80000 | +-------------+ | 157080 | +-------------+ 68
85. Optiomizer Traces For those who want to go deeper, the MySQL Optimizer provides a trace feature. SQL SQL> > set set optimizer_trace optimizer_trace= ="enabled=on" "enabled=on"; ; SQL SQL> > explain explain format format= =tree tree < <your query your query> >; ; SQL SQL> > select select * * from from information_schema information_schema. .optimizer_trace\G optimizer_trace\G . .. .. . ENJOY ENJOY ; ;- -) ) . .. .. . SQL SQL> > set set optimizer_trace optimizer_trace= ="enabled=off" "enabled=off"; ; Copyright @ 2025 Oracle and/or its affiliates. 69
86. Let's try to optimize our Ugly Duckling We can see that the query is using a temporary table and a �lesort, and it starts by performing a full table scan on the products table. The �rst think to do is to check both tables: SQL SQL> > desc desc products products; ; + +-------------+--------------+------+-----+-------------------+-------------------+ -------------+--------------+------+-----+-------------------+-------------------+ | | Field Field | | Type Type | | Null Null | | Key Key | | Default Default | | Extra Extra | | + +-------------+--------------+------+-----+-------------------+-------------------+ -------------+--------------+------+-----+-------------------+-------------------+ | | name name | | varchar varchar( (255 255) ) | | YES YES | | | | NULL NULL | | | | | | description description | | varchar varchar( (150 150) ) | | YES YES | | | | NULL NULL | | | | | | price price | | float float | | YES YES | | | | NULL NULL | | | | | | created_at created_at | | datetime datetime | | YES YES | | | | CURRENT_TIMESTAMP CURRENT_TIMESTAMP | | DEFAULT_GENERATED DEFAULT_GENERATED | | + +-------------+--------------+------+-----+-------------------+-------------------+ -------------+--------------+------+-----+-------------------+-------------------+ Copyright @ 2025 Oracle and/or its affiliates. 70
87. Let's try to optimize our Ugly Duckling (2) SQL SQL> > desc desc reviews reviews; ; + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ | | Field Field | | Type Type | | Null Null | | Key Key | | Default Default | | Extra Extra | | + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ | | user_id user_id | | varchar varchar( (36 36) ) | | YES YES | | MUL MUL | | NULL NULL | | | | | | product_id product_id | | int int | | YES YES | | MUL MUL | | NULL NULL | | | | | | rating rating | | int int | | YES YES | | | | NULL NULL | | | | | | review review | | text text | | YES YES | | | | NULL NULL | | | | | | created_at created_at | | datetime datetime | | YES YES | | | | CURRENT_TIMESTAMP CURRENT_TIMESTAMP | | DEFAULT_GENERATED DEFAULT_GENERATED | | | | id id | | int int unsigned unsigned | | NO NO | | PRI PRI | | NULL NULL | | auto_increment auto_increment | | + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ Copyright @ 2025 Oracle and/or its affiliates. 71
88. Let's try to optimize our Ugly Duckling (2) SQL SQL> > desc desc reviews reviews; ; + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ | | Field Field | | Type Type | | Null Null | | Key Key | | Default Default | | Extra Extra | | + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ | | user_id user_id | | varchar varchar( (36 36) ) | | YES YES | | MUL MUL | | NULL NULL | | | | | | product_id product_id | | int int | | YES YES | | MUL MUL | | NULL NULL | | | | | | rating rating | | int int | | YES YES | | | | NULL NULL | | | | | | review review | | text text | | YES YES | | | | NULL NULL | | | | | | created_at created_at | | datetime datetime | | YES YES | | | | CURRENT_TIMESTAMP CURRENT_TIMESTAMP | | DEFAULT_GENERATED DEFAULT_GENERATED | | | | id id | | int int unsigned unsigned | | NO NO | | PRI PRI | | NULL NULL | | auto_increment auto_increment | | + +------------+--------------+------+-----+-------------------+-------------------+ ------------+--------------+------+-----+-------------------+-------------------+ Copyright @ 2025 Oracle and/or its affiliates. Remember the query is: SELECT products.name, COUNT(reviews.rating) AS review_count FROM ecommerce.products LEFT JOIN ecommerce.reviews ON products.id = reviews.product_id GROUP BY products.name ORDER BY review_count DESC LIMIT 1\G And the cost was 51674.7939157486 71
89. Let's try to optimize our Ugly Duckling (3) Then we relalize we could replace the LEFT JOIN by a JOIN: SQL SQL> > SELECT SELECT products products. .name name, , COUNT COUNT( (reviews reviews. .rating rating) ) AS AS review_count review_count FROM FROM ecommerce ecommerce. .products products JOIN JOIN ecommerce ecommerce. .reviews reviews ON ON products products. .id id = = reviews reviews. .product_id product_id GROUP GROUP BY BY products products. .name name ORDER ORDER BY BY review_count review_count DESC DESC LIMIT LIMIT 1 1\G \G EXPLAIN: -> Limit: 1 row(s) -> Sort: review_count DESC, limit input to 1 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=12519 rows=27588) -> Filter: (reviews.product_id is not null) (cost=2863 rows=27588) -> Table scan on reviews (cost=2863 rows=27588) -> Single-row index lookup on products using PRIMARY (id = reviews.product_id) (cost=0.25 rows=1) Copyright @ 2025 Oracle and/or its affiliates. 72
90. Let's try to optimize our Ugly Duckling (3) Then we relalize we could replace the LEFT JOIN by a JOIN: SQL SQL> > SELECT SELECT products products. .name name, , COUNT COUNT( (reviews reviews. .rating rating) ) AS AS review_count review_count FROM FROM ecommerce ecommerce. .products products JOIN JOIN ecommerce ecommerce. .reviews reviews ON ON products products. .id id = = reviews reviews. .product_id product_id GROUP GROUP BY BY products products. .name name ORDER ORDER BY BY review_count review_count DESC DESC LIMIT LIMIT 1 1\G \G EXPLAIN: -> Limit: 1 row(s) -> Sort: review_count DESC, limit input to 1 row(s) per chunk -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=12519 rows=27588) -> Filter: (reviews.product_id is not null) (cost=2863 rows=27588) -> Table scan on reviews (cost=2863 rows=27588) -> Single-row index lookup on products using PRIMARY (id = reviews.product_id) (cost=0.25 rows=1) Copyright @ 2025 Oracle and/or its affiliates. We have now the following cost: +------------------+ | max_query_cost | +------------------+ | 12518.8500000000 | +------------------+ 72
91. Let's try to optimize our Ugly Duckling be�er is the enemy of good We could have created an index on (product_id, rating) in the reviews table and group by product_id, but the cost would have been higher: EXPLAIN: -> Limit: 1 row(s) -> Sort: review_count DESC, limit input to 1 row(s) per chunk -> Stream results (cost=18876 rows=19035) -> Group aggregate: count(reviews.rating) (cost=18876 rows=19035) -> Nested loop inner join (cost=12519 rows=27588) -> Filter: (reviews.product_id is not null) (cost=2863 rows=27588) -> Covering index scan on reviews using prd_rat_idx (cost=2863 rows=27588) -> Single-row index lookup on products using PRIMARY (id = reviews.product_id) (cost=0.25 rows=1) The cost is now 18875.5320276498 Copyright @ 2025 Oracle and/or its affiliates. 73
92. Recap: optimize your queries • use EXPLAIN to get the Query Execution Plan • use EXPLAIN ANALYZE to get the real numbers • compare the costs of di�erent queries • verify QEP and cost over time (I encourage you to save them) ◦ use statement_digest_text() ◦ trim the output and hash it to save the query ▪ SHA2(TRIM(statement_digest_text(<query>)), 224) Copyright @ 2025 Oracle and/or its affiliates. 74
93. Copyright @ 2025 Oracle and/or its affiliates. 75
94. Split your workload reads and writes Copyright @ 2025 Oracle and/or its affiliates. 76
95. Best Practices: split your workload It's always a good idea to split your workload between reads and writes using dedicated connections. Even if you are using the same server to start with. Such con�guration will allow you to scale your infrastructure more easily and the DBAs and/or OPS will be very happy. conn_r conn_r = = DriverManager DriverManager. .getConnection getConnection( ("jdbc:mysql://localhost/ecommerce?" "jdbc:mysql://localhost/ecommerce?" + + "user=reader&password=greatsqldb" "user=reader&password=greatsqldb") ); ; conn_w conn_w = = DriverManager DriverManager. .getConnection getConnection( ("jdbc:mysql://localhost/ecommerce?" "jdbc:mysql://localhost/ecommerce?" + + "user=writer&password=bestsqldb" "user=writer&password=bestsqldb") ); ; Copyright @ 2025 Oracle and/or its affiliates. 77
96. Best Practices: split your workload (2) You can also use di�erent servers for reads and writes and then you will be prepared to scale your infrastructure like this: Copyright @ 2025 Oracle and/or its affiliates. 78
97. Best Practices: split your workload (3) Some connectors, including MySQL Connector/J support multi-host connections. Or you can use MySQL Router to manage the read/write split transparently for you. Copyright @ 2025 Oracle and/or its affiliates. 79
98. Fail-over Connections (JDBC) jdbc jdbc: :mysql mysql: :/ // /primary primary: :port port, ,secondary1 secondary1: :port port, ,secondary2 secondary2: :port port, ,. .. .. ./dbname /dbname? ?props props. .. .. . • Connection-related errors trigger fail-over to a secondary host • Connection errors are propagated to the client, that must handle them • Fall-back to primary host occurs transparent and automatically, based on: ◦ secondsBeforeRetrySource - the time to wait before falling back ◦ queriesBeforeRetrySource - number of queries before falling back • With autoReconnect=true the same Statement objects can be reused, otherwise new ones must be created Copyright @ 2025 Oracle and/or its affiliates. 80
99. Load-balancing Connections (JDBC) jdbc jdbc: :mysql mysql: :loadbalance loadbalance: :/ // /host1 host1: :port port, ,host2 host2: :port port, ,host3 host3: :port port, ,. .. .. ./database /database? ?props props. .. .. . • Load is distributed among the listed hosts based on customizable load-balancing strategies - ha.loadBalanceStrategy: ◦ Native: random, bestResponseTime and serverAf�nity (pairs with serverAf�nityOrder) ◦ Custom: implementation of com.mysql.cj.jdbc.JdbcConnection.BalanceStrategy • Load balancing/fail-over occurs on: ◦ Transaction boundaries: explicit commit/rollback ◦ Connection exceptions: SQL State “08xxx” ◦ Conditions de�ned by user: ▪ loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker Copyright @ 2025 Oracle and/or its affiliates. 81
100. Replication-aware Connections (JDBC) jdbc jdbc: :mysql mysql: :replication replication: :/ // /source source: :port port, ,replica1 replica1: :port port, ,replica2 replica2: :port port, ,. .. .. ./database /database? ?props props. .. .. . • Connections are initialized with two sub-connections: ◦ One load-balanced source(s) connection ◦ One load-balanced replicas connection • Connection.setReadOnly() switches between sources and replicas sub-connections • Replication-aware connections speci�c options: ◦ allowSourceDownConnections - use replicas if no sources or all down, on connection initialization ◦ allowReplicaDownConnections - use sources if no replicas or all down, on connection initialization ◦ readFromSourceWhenNoReplicas - user the sources connection, in read-only, if no replicas or all down • Support for multi-source replication topologies Copyright @ 2025 Oracle and/or its affiliates. 82
101. Recap: prepare your infrastructure for scaling • split your workload between reads and writes • use di�erent servers for reads and writes • use multi-host connections • use MySQL Router to manage the read/write split Copyright @ 2025 Oracle and/or its affiliates. 83
102. Hey Java Users! some words about MySQL-Connector-J Copyright @ 2025 Oracle and/or its affiliates. 84
103. MySQL Connector/J Tweaking performance: • useLocalSessionState - keep a session state local reference for auto-commit and transaction isolation values to reduce the number of transaction handling queries to send to the server • useLocalTransactionState - rely on protocol to determine if commit() or rollback() should be sent to the database • cacheServerCon�guration - cache server variables and collation data from �rst connection for using in the following connections • alwaysSendSetIsolation - either always send or only if di�erent, transaction isolation se�ings, in Connection.setTransactionIsolation() Copyright @ 2025 Oracle and/or its affiliates. 85
104. MySQL Connector/J (2) • elideSetAutoCommits - either always send or only if di�erent, session auto-commit se�ings, in Connection.setAutocommit() • readOnlyPropagatesToServer - sets server-side read-only state or manages it entirely on client-side • cachePrepStmts/cacheCallableStmts - cache the parsing stage of client-side prepared/callable statements and server-prepared statements instances • cacheResultSetMetadata - should the driver cache ResultSetMetaData for statements and prepared statements? Copyright @ 2025 Oracle and/or its affiliates. 86
105. MySQL Connector/J (3) • rewriteBatchedStatements - rewrite batched inserts e�ciently and/or use multi- queries to reduce the number of queries to send to the database • useCursorFetch - combined with Statement.setFetchSize() instructs the server to user cursor-based server-side prepared statements executions • dontCheckOnDuplicateKeyUpdateInSQL - stops checking if every INSERT statement contains the ON DUPLICATE KEY UPDATE clause Copyright @ 2025 Oracle and/or its affiliates. 87
106. Single Statement, multiple queries MySQL Connector/J supports executing multiple statements at once if se�ing allowMultiQueries=true • queries are given one after the other with a semi-colon (;) in between • statement may produce multiple result sets • hard to handle queries that result in execution errors Disabled by default • helps preventing some SQL injection vulnerabilities ◦ … it's harder to inject artifacts that don't result in multiple queries: name = "foo'); drop table t; --" Copyright @ 2025 Oracle and/or its affiliates. 88
107. Single Statement, multiple queries (2) Not available in server-prepared statements • MySQL server only supports preparing single queries • automatically fall back to client-prepared statements Copyright @ 2025 Oracle and/or its affiliates. 89
108. Rewriting Batches Se�ing rewriteBatchedStatements=true enables replacing statement batches by single multi-statement executions • a�ects [Prepared]Statement.add/executeBatch() • reduces the latency caused by one execution per batch entry • special treatment for INSERT statements • in�uences the behavior speci�ed continueBatchOnError Copyright @ 2025 Oracle and/or its affiliates. 90
109. Rewriting Batches (2) Plain statements rewri�en by concatenating one after the other • INSERT INTO t VALUES (...); INSERT INTO t VALUES (...); ... • overrides allowMultiQueries • turn on when batch size > 3 Prepared statements of INSERT or REPLACE rewri�en as multi-VALUES clause • INSERT INTO t VALUES (?) --> INSERT INTO t VALUES (?), (?), (?), … • supports ON DUPLICATE KEY UPDATE, except when using LAST_INSERT_ID() Copyright @ 2025 Oracle and/or its affiliates. 91
110. Caching Statements and Results Metadata Preparing phase of prepared statements can be cached for improved performance • Enabled by cachePrepStmts=true and con�gured with prepStmtCacheSize and prepStmtCacheSqlLimit • Client-prepared statements cache keeps copies of the query parsing information • Server-prepared statements cache keeps copies of statements prepared on the server ◦ to be used diligently as MySQL server limits the maximum number of active prepared statements ◦ used together with connection pools can speed up resource's exhaustion or cause DoS Copyright @ 2025 Oracle and/or its affiliates. 92
111. Caching Statements and Results Metadata (2) Similar functionality for callable statements • enabled and con�gured with cacheCallableStmts, callableStmtCacheSize Results metadata caching can be enabled with cacheResultSetMetadata=true • allows reusing metadata from previously executed queries • enables skipping metadata processing for repeating queries Caches introduce the risk of missing DDL changes Copyright @ 2025 Oracle and/or its affiliates. 93
112. Recap: optimize your MySQL Connector/J There are plenty con�guration se�ings that can be used to optimize the performance of your MySQL Connector/J. Depending of your workload, you can set the right values for these se�ings. Copyright @ 2025 Oracle and/or its affiliates. 94
113. MySQL & InnoDB the secret variables making all the di�erence Copyright @ 2025 Oracle and/or its affiliates. 95
114. DBA's job Tunning InnoDB is more a DBA's job than a developer's job. But aren't we speaking about "full stack developers" ? So let me share some secrets with you to make your application faster. Copyright @ 2025 Oracle and/or its affiliates. 96
115. DBA's job Tunning InnoDB is more a DBA's job than a developer's job. But aren't we speaking about "full stack developers" ? So let me share some secrets with you to make your application faster. Secret #1: Copyright @ 2025 Oracle and/or its affiliates. 96
116. DBA's job Tunning InnoDB is more a DBA's job than a developer's job. But aren't we speaking about "full stack developers" ? So let me share some secrets with you to make your application faster. Secret #1: memory is fast! Copyright @ 2025 Oracle and/or its affiliates. 96
117. InnoDB Bu�er Pool It's important to have the Working Set in memory ! Less your read from disk faster your queries will be. Copyright @ 2025 Oracle and/or its affiliates. 97
118. InnoDB Bu�er Pool It's important to have the Working Set in memory ! Less your read from disk faster your queries will be. We can verify that most of the page requests are coming from memory: SQL SQL> > show show global global status status like like 'innodb_buffer_pool_read%s' 'innodb_buffer_pool_read%s'; ; + +----------------------------------+------------+ ----------------------------------+------------+ | | Variable_name Variable_name | | Value Value | | + +----------------------------------+------------+ ----------------------------------+------------+ | | Innodb_buffer_pool_read_requests Innodb_buffer_pool_read_requests | | 1201291089 1201291089 | | | | Innodb_buffer_pool_reads Innodb_buffer_pool_reads | | 1986 1986 | | + +----------------------------------+------------+ ----------------------------------+------------+ 2 2 rows rows in in set set ( (0.0069 0.0069 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 97
119. InnoDB Bu�er Pool (2) We need to keep the ratio between pages requested and pages read from disk as low as possible. SQL SQL> > select select concat concat( (format format( (B B. .num num * * 100.0 100.0 / / A A. .num num, ,2 2) ), ,'%' '%') ) DiskReadRatio DiskReadRatio from from ( ( select select variable_value num variable_value num from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name = = 'Innodb_buffer_pool_read_requests' 'Innodb_buffer_pool_read_requests') ) A A, , ( ( select select variable_value num variable_value num from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name = = 'Innodb_buffer_pool_reads' 'Innodb_buffer_pool_reads') ) B B; ; + +---------------+ ---------------+ | | DiskReadRatio DiskReadRatio | | + +---------------+ ---------------+ | | 0.00 0.00% % | | + +---------------+ ---------------+ 1 1 row row in in set set ( (0.0008 0.0008 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 98
120. InnoDB Bu�er Pool (3) If the ratio is above 10%, I would recomment to increase the size of the Bu�er Pool and/or check if less pages could be read from disk by reducing the working set (less full table scans, archiving old data, using partitioning, etc). Check what's in the Bu�er Pool, you might be surprised: SQL SQL> > SELECT SELECT TABLE_NAME TABLE_NAME, ,INDEX_NAME INDEX_NAME, , COUNT COUNT( (* *) ) AS AS Pages Pages, , ROUND ROUND( (SUM SUM( (IF IF( (COMPRESSED_SIZE COMPRESSED_SIZE = = 0 0, , 16384 16384, , COMPRESSED_SIZE COMPRESSED_SIZE) )) )/ /1024 1024/ /1024 1024) ) AS AS 'Total Data (MB)' 'Total Data (MB)' FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .INNODB_BUFFER_PAGE INNODB_BUFFER_PAGE WHERE WHERE table_name table_name not not like like '`mysql`.%' '`mysql`.%' GROUP GROUP BY BY TABLE_NAME TABLE_NAME, ,INDEX_NAME INDEX_NAME order order by by 4 4 desc desc, ,3 3 desc desc; ; Copyright @ 2025 Oracle and/or its affiliates. 99
121. InnoDB Bu�er Pool (3) We can verify the Bu�er Pool's usage with this query: SQL SQL> > SELECT SELECT format_bytes format_bytes( (@ @@innodb_buffer_pool_size @innodb_buffer_pool_size) ) BufferPoolSize BufferPoolSize, , FORMAT FORMAT( (A A. .num num * * 100.0 100.0 / / B B. .num num, ,2 2) ) BufferPoolFullPct BufferPoolFullPct, , FORMAT FORMAT( (C C. .num num * * 100.0 100.0 / / D D. .num num, ,2 2) ) BufferPollDirtyPct BufferPollDirtyPct FROM FROM ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_data' 'Innodb_buffer_pool_pages_data') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) B B, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_dirty' 'Innodb_buffer_pool_pages_dirty') ) C C, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) D D; ; Copyright @ 2025 Oracle and/or its affiliates. 100
122. InnoDB Bu�er Pool (3) We can verify the Bu�er Pool's usage with this query: SQL SQL> > SELECT SELECT format_bytes format_bytes( (@ @@innodb_buffer_pool_size @innodb_buffer_pool_size) ) BufferPoolSize BufferPoolSize, , FORMAT FORMAT( (A A. .num num * * 100.0 100.0 / / B B. .num num, ,2 2) ) BufferPoolFullPct BufferPoolFullPct, , FORMAT FORMAT( (C C. .num num * * 100.0 100.0 / / D D. .num num, ,2 2) ) BufferPollDirtyPct BufferPollDirtyPct FROM FROM ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_data' 'Innodb_buffer_pool_pages_data') ) A A, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name = = 'Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) B B, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_dirty' 'Innodb_buffer_pool_pages_dirty') ) C C, , ( (SELECT SELECT variable_value num variable_value num FROM FROM performance_schema performance_schema. .global_status global_status WHERE WHERE variable_name variable_name= ='Innodb_buffer_pool_pages_total' 'Innodb_buffer_pool_pages_total') ) D D; ; Copyright @ 2025 Oracle and/or its affiliates. +----------------+-------------------+--------------------+ | BufferPoolSize | BufferPoolFullPct | BufferPollDirtyPct | +----------------+-------------------+--------------------+ | 128.00 MiB | 69.10 | 8.17 | +----------------+-------------------+--------------------+ 100
123. InnoDB Bu�er Pool - Warm Bu�er Pool As developer, you might start a MySQL instance, execute some queries and then stop the instance. But the performance of such queries could be not optimal as the Bu�er Pool is cold. MySQL provides a solution for that. Copyright @ 2025 Oracle and/or its affiliates. 101
124. InnoDB Bu�er Pool - Warm Bu�er Pool (2) To always start with a warm Bu�er Pool, you can dump the content of the InnoDB Bu�er Pool to disk and load it at startup: SQL SQL> > set set persist innodb_buffer_pool_dump_at_shutdown persist innodb_buffer_pool_dump_at_shutdown = = 1 1; ; SQL SQL> > set set persist innodb_buffer_pool_load_at_startup persist innodb_buffer_pool_load_at_startup = = 1 1; ; Copyright @ 2025 Oracle and/or its affiliates. 102
125. InnoDB Bu�er Pool - Warm Bu�er Pool (2) To always start with a warm Bu�er Pool, you can dump the content of the InnoDB Bu�er Pool to disk and load it at startup: SQL SQL> > set set persist innodb_buffer_pool_dump_at_shutdown persist innodb_buffer_pool_dump_at_shutdown = = 1 1; ; SQL SQL> > set set persist innodb_buffer_pool_load_at_startup persist innodb_buffer_pool_load_at_startup = = 1 1; ; In production, I would recommend to dump the content of the BP at regular intervals has the working set could change quickly over time. SQL SQL> > create create event automatic_bufferpool_dump event automatic_bufferpool_dump on on schedule every schedule every 1 1 hour hour do do set set global global innodb_buffer_pool_dump_now innodb_buffer_pool_dump_now= =on on; ; Copyright @ 2025 Oracle and/or its affiliates. 102
126. InnoDB Redo Log Capacity If your database is write intensive, having a correct redo log capacity is important to avoid stalls. The rule of thumb is to have a redo log capacity of 1 hour to allow InnoDB's checkpointing to run smoothly. This se�ing is controlled by the variable innodb_log_capacity. Copyright @ 2025 Oracle and/or its affiliates. 103
127. InnoDB Redo Log Capacity (2) The best way to �nd the adequate value is to run the following query (on single line) at peak time: SQL SQL> > select select variable_value variable_value from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name= ='innodb_redo_log_current_lsn' 'innodb_redo_log_current_lsn' into into @a @a; ;select select sleep sleep( (60 60) ) into into @garb @garb; ; select select variable_value variable_value from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name= ='innodb_redo_log_current_lsn' 'innodb_redo_log_current_lsn' into into @b @b; ;select select format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )) ) per_min per_min, , format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )* *60 60) ) per_hour per_hour; ; Query OK Query OK, , 1 1 row row affected affected ( (0.0005 0.0005 sec sec) ) Query OK Query OK, , 1 1 row row affected affected ( (1 1 min min 0.0002 0.0002 sec sec) ) Query OK Query OK, , 1 1 row row affected affected ( (0.0006 0.0006 sec sec) ) + +----------+------------+ ----------+------------+ | | per_min per_min | | per_hour per_hour | | + +----------+------------+ ----------+------------+ | | 5.69 5.69 MiB MiB | | 341.11 341.11 MiB MiB | | + +----------+------------+ ----------+------------+ 1 1 row row in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 104
128. InnoDB Redo Log Capacity (2) The best way to �nd the adequate value is to run the following query (on single line) at peak time: SQL SQL> > select select variable_value variable_value from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name= ='innodb_redo_log_current_lsn' 'innodb_redo_log_current_lsn' into into @a @a; ;select select sleep sleep( (60 60) ) into into @garb @garb; ; select select variable_value variable_value from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name= ='innodb_redo_log_current_lsn' 'innodb_redo_log_current_lsn' into into @b @b; ;select select format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )) ) per_min per_min, , format_bytes format_bytes( (abs abs( (@a @a - - @b @b) )* *60 60) ) per_hour per_hour; ; Query OK Query OK, , 1 1 row row affected affected ( (0.0005 0.0005 sec sec) ) Query OK Query OK, , 1 1 row row affected affected ( (1 1 min min 0.0002 0.0002 sec sec) ) Query OK Query OK, , 1 1 row row affected affected ( (0.0006 0.0006 sec sec) ) + +----------+------------+ ----------+------------+ | | per_min per_min | | per_hour per_hour | | + +----------+------------+ ----------+------------+ | | 5.69 5.69 MiB MiB | | 341.11 341.11 MiB MiB | | + +----------+------------+ ----------+------------+ 1 1 row row in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. SQL> set persist innodb_redo_log_capacity=350*1024*1024; 104
129. Recap: keep your working set in memory • check the Bu�er Pool usage • keep the ratio between pages requested and pages read from disk as low as possible Copyright @ 2025 Oracle and/or its affiliates. 105
130. Recap: use a Bu�er Pool always warm • dump the content of the Bu�er Pool at shutdown • dump regularly the content of the Bu�er Pool using an event • load the content of the Bu�er Pool at startup Copyright @ 2025 Oracle and/or its affiliates. 106
131. Recap: have a correct redo log capacity • check the redo log capacity • set the redo log capacity to 1 hour Copyright @ 2025 Oracle and/or its affiliates. 107
132. Recap: let MySQL setup InnoDB for you On a dedicated MySQL Server, the best is to let InnoDB decide the size of the Bu�er Pool and the Redo Log Capacity. In my.cnf: innodb_dedicated_server innodb_dedicated_server= =1 1 See h�ps://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html Copyright @ 2025 Oracle and/or its affiliates. 108
133. Add some limits to avoid bad surprises Copyright @ 2025 Oracle and/or its affiliates. 109
134. Cap the Query Time It's possible to stop the execution of a query, SELECT(*) , if it takes too long. The value of "too long" is de�ned in the variable max_execution_time or using an optimizer hint: select select /*+ max_execution_time(5000) */ /*+ max_execution_time(5000) */ sleep sleep( (10 10) ); ; + +-----------+ -----------+ | | sleep sleep( (10 10) ) | | + +-----------+ -----------+ | | 1 1 | | + +-----------+ -----------+ 1 1 row row in in set set ( (5.0006 5.0006 sec sec) ) (*) not part of a store procedure Copyright @ 2025 Oracle and/or its affiliates. 110
135. Cap the amount of returned rows Of course you can use the LIMIT keyword to limit the amount of rows returned by a query. But it's also possible to limit the amount of rows returned by a query using a variable (session or global) to avoid bad surprises. Copyright @ 2025 Oracle and/or its affiliates. 111
136. Cap the amount of returned rows (2) SQL SQL> > select select count count( (* *) ) from from orders orders; ; + +----------+ ----------+ | | count count( (* *) ) | | + +----------+ ----------+ | | 3359 3359 | | + +----------+ ----------+ SQL SQL> > set set sql_select_limit sql_select_limit= =5 5; ; SQL SQL> > select select * * from from orders orders; ; + +----+--------------------------------------+-------------+---------------------+ ----+--------------------------------------+-------------+---------------------+ | | id id | | user_id user_id | | total_price total_price | | created_at created_at | | + +----+--------------------------------------+-------------+---------------------+ ----+--------------------------------------+-------------+---------------------+ | | 1 1 | | a2293479 a2293479- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | 841.21 841.21 | | 2025 2025- -02 02- -25 25 10 10: :27 27: :54 54 | | | | 2 2 | | a21e7b98 a21e7b98- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | 417.41 417.41 | | 2025 2025- -02 02- -25 25 10 10: :27 27: :54 54 | | | | 3 3 | | a3e980a6 a3e980a6- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | 2163.46 2163.46 | | 2025 2025- -02 02- -25 25 10 10: :27 27: :54 54 | | | | 4 4 | | 9 9f4b2ef2 f4b2ef2- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | 1640.24 1640.24 | | 2025 2025- -02 02- -25 25 10 10: :27 27: :54 54 | | | | 5 5 | | a23e395c a23e395c- -f35a f35a- -11 11ef ef- -9704 9704- -5 5e1b9081e705 e1b9081e705 | | 3090.51 3090.51 | | 2025 2025- -02 02- -25 25 10 10: :27 27: :54 54 | | + +----+--------------------------------------+-------------+---------------------+ ----+--------------------------------------+-------------+---------------------+ 5 5 rows rows in in set set ( (0.0003 0.0003 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 112
137. Connections Tracking and Limiting To avoid bad surprises (like swapping), it's possible to track and limit the memory consumption of the connections. To enable it you need to set global_connection_memory_tracking to 1: SQL SQL> > set set global global global_connection_memory_tracking global_connection_memory_tracking= =1 1; ; Copyright @ 2025 Oracle and/or its affiliates. 113
138. Connections Tracking and Limiting To avoid bad surprises (like swapping), it's possible to track and limit the memory consumption of the connections. To enable it you need to set global_connection_memory_tracking to 1: SQL SQL> > set set global global global_connection_memory_tracking global_connection_memory_tracking= =1 1; ; You can limit the connection memory limit: SQL SQL> > set set < <global global/ /session session> > connection_memory_limit connection_memory_limit= =2200000 2200000; ; SQL SQL> > set set global global global_connection_memory_limit global_connection_memory_limit= =536870912000 536870912000; ; Copyright @ 2025 Oracle and/or its affiliates. 113
139. Connections Tracking and Limiting (2) To know the Global Connection Consumption Memory: SQL SQL> > select select format_bytes format_bytes( (variable_value variable_value) ) global_connection_memory global_connection_memory from from performance_schema performance_schema. .global_status global_status where where variable_name variable_name= ='global_connection_memory' 'global_connection_memory'; ; + +--------------------------+ --------------------------+ | | global_connection_memory global_connection_memory | | + +--------------------------+ --------------------------+ | | 16.22 16.22 MiB MiB | | + +--------------------------+ --------------------------+ Copyright @ 2025 Oracle and/or its affiliates. 114
140. Connections Tracking and Limiting (3) If the limit it reached, the user will be disconnected with the following error: ERROR: 4081 (HY000): Connection closed. Global connection memory limit 16777216 bytes exceeded. Consumed 16949968 bytes. This limitation doesn't appy to users with CONNECTION_ADMIN privilege. Copyright @ 2025 Oracle and/or its affiliates. 115
141. Don't wait It's possible to skip to wait for timeout when a lock is set on a row. Depending on your use case, you can decide to not wait or skip the locked rows. Let's see how to do that. In one session, we do: Session Session 1 1 SQL SQL> > start start transaction transaction; ; Session Session 1 1 SQL SQL> > update update reviews reviews set set rating rating= =rating rating+ +1 1 where where product_id product_id= =26719 26719; ; Query OK Query OK, , 7 7 rows rows affected affected ( (0.0004 0.0004 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 116
142. Don't wait (2) This is the usual behaviour when a lock is set on row(s): Session Session 2 2 SQL SQL> > select select user_id user_id, , rating rating from from reviews reviews where where product_id product_id= =26719 26719 for for update update ; ; ERROR: ERROR: 1205 1205 ( (HY000 HY000) ): : Lock Lock wait timeout exceeded wait timeout exceeded; ; try restarting try restarting transaction transaction Session Session 2 2 SQL SQL> > show show variables variables like like 'innodb_lock_wait_timeout' 'innodb_lock_wait_timeout'; ; + +--------------------------+-------+ --------------------------+-------+ | | Variable_name Variable_name | | Value Value | | + +--------------------------+-------+ --------------------------+-------+ | | innodb_lock_wait_timeout innodb_lock_wait_timeout | | 50 50 | | + +--------------------------+-------+ --------------------------+-------+ 1 1 row row in in set set ( (0.0021 0.0021 sec sec) ) Copyright @ 2025 Oracle and/or its affiliates. 117
143. Session Session 2 2 SQL SQL> > select select user_id user_id, , rating rating from from reviews reviews where where product_id product_id= =26719 26719 for for update update nowait nowait; ; ERROR: ERROR: 3572 3572 ( (HY000 HY000) ): Statement aborted because : Statement aborted because lock lock( (s s) ) could could not not be acquired immediately be acquired immediately and and NOWAIT NOWAIT is is set set. . Don't wait (3) Now let's try to use NOWAIT and SKIP LOCKED in two di�erent sessions: Copyright @ 2025 Oracle and/or its affiliates. 118
144. Session Session 2 2 SQL SQL> > select select user_id user_id, , rating rating from from reviews reviews where where product_id product_id= =26719 26719 for for update update nowait nowait; ; ERROR: ERROR: 3572 3572 ( (HY000 HY000) ): Statement aborted because : Statement aborted because lock lock( (s s) ) could could not not be acquired immediately be acquired immediately and and NOWAIT NOWAIT is is set set. . Session Session 3 3 SQL SQL> > select select user_id user_id, , rating rating from from reviews reviews where where product_id product_id= =26719 26719 for for update update skip locked skip locked; ; Empty Empty set set ( (0.0008 0.0008 sec sec) ) Don't wait (3) Now let's try to use NOWAIT and SKIP LOCKED in two di�erent sessions: Copyright @ 2025 Oracle and/or its affiliates. 118
145. Recap: �x limits • cap the query time • cap the amount of returned rows • limit the memory consumption of the connections • don't wait for locks Copyright @ 2025 Oracle and/or its affiliates. 119
146. Share your to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2025 Oracle and/or its affiliates. 120
147. Questions ? Copyright @ 2025 Oracle and/or its affiliates. 121