Efficient MySQL Indexing and what's new in MySQL Explain

Efficient MySQL Indexing & What's New in MySQL Explain - Mydbops MyWebinar Edition 32

This session will delve into:

• Strategic indexing techniques: Learn how to optimize your MySQL database by implementing effective indexing strategies, including when to avoid fulltext indexes to prevent wasted resources.
• Demystifying the new MySQL Explain: We'll explore the latest enhancements to the MySQL Explain plan's JSON output format. Discover how to store the output in a variable for further analysis – a valuable addition introduced in MySQL 8.3. You'll also learn about the explain_json_format_version variable, which empowers you to choose between different JSON output versions for greater flexibility.
• Live Chat Engagement: We encourage you to actively participate throughout the webinar! Use the chat functionality to ask questions and share your experiences with indexing and Explain.

This webinar is perfect for:
• Database administrators (DBAs)
• Developers
• Anyone seeking to optimize MySQL performance and streamline database queries

Mydbops Managed Services specializes in taking the pain out of database management while optimizing performance. Since 2015, we have been providing top-notch support and assistance for the top three open-source databases: MySQL, MongoDB, and PostgreSQL.

Our team offers a wide range of services, including assistance, support, consulting, 24/7 operations, and expertise in all relevant technologies. We help organizations improve their database's performance, scalability, efficiency, and availability.

Contact us: [email protected]

Visit: https://www.mydbops.com/

1. Efficient MySQL Indexing &
What's New in MySQL Explain
Presented by
Jebashalomie Immanuel
Mydbops
Mydbops MyWebinar - 32
May 11th, 2024

2. Consulting
Services
Consulting
Services
Managed
Services
●Database Management and
consultancy provider
●Founded in 2016
●Assisted 800+ happy customers
●AWS partners
●PCI & ISO certified

About Us

3. Agenda
❏Index Overview
❏Types of Indexes
❏Increasing Indexing Efficiency
❏Example
❏Before Optimization
❏Optimization
❏After Optimization
❏New Features
❏Capturing EXPLAIN FORMAT=JSON Output
❏explain_json_format_version

4. Index Overview

5. Index Overview
❏Enhances data retrieval speed, uses extra space.

❏Created on table columns, choosing the right index is vital.

❏Needs regular updates to stay efficient.

❏Index types: Includes primary, unique, composite, and full-text.

6. Types of Indexes

7. Types of Indexes

8. Increasing Index Efficiency

9. Increasing Index Efficiency
❏Analysing the queries

❏Avoid over indexing

❏Analyze the cardinality

❏Pick the correct columns

❏Pick the suitable index

❏Regular maintenance

10. Example

11. Example
Datatype:
column1 - text

Query:
SELECT * FROM test_function WHERE column1 LIKE
'flexibility';

Total rows: 96

12. Before Optimization

13. Before Optimization
mysql> explain SELECT * FROM test_function WHERE column1 LIKE 'flexibility'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_function
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96
filtered: 11.11
Extra: Using where

14. Optimization

Index creation:
mysql> Alter table test_function add fulltext index idx_column1(column1);
Query OK, 0 rows affected, 1 warning (0.20 sec)




mysql> explain SELECT * FROM test_function WHERE column1 LIKE 'flexibility';
+----+-------------+---------------+------------+------+---------------+------+---------+------+-
-----+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref |
rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-
-----+----------+-------------+
| 1 | SIMPLE | test_function | NULL | ALL | idx_column1 | NULL | NULL | NULL |
96 | 11.11 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-
-----+----------+-------------+

15. After Optimization

16. After Optimization

mysql> EXPLAIN SELECT * FROM test_function WHERE MATCH(column1) AGAINST
('flexibility')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_function
partitions: NULL
type: fulltext
possible_keys: idx_column1
key: idx_column1
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM test_function WHERE MATCH(column1) AGAINST ('flexibility')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_function
partitions: NULL
type: fulltext
possible_keys: idx_column1
key: idx_column1
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)

17. New Features
❏Capturing EXPLAIN FORMAT=JSON output (MySQL 8.1.0)
❏explain_json_format_version (MySQL 8.3.0)

18. Capturing EXPLAIN FORMAT=JSON Output
Query:

mysql> EXPLAIN FORMAT=JSON INTO @myvar SELECT * FROM city WHERE
District='Texas';
Query OK, 0 rows affected (0.00 sec)


mysql> EXPLAIN FORMAT=JSON INTO @myvar SELECT * FROM city WHERE
District='Texas';
Query OK, 0 rows affected (0.00 sec)

19. mysql> select @myvar\G
*************************** 1. row ***************************
@myvar: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "428.50"
},
"table": {
"table_name": "city",
"access_type": "ALL",
"rows_examined_per_scan": 4035,
"rows_produced_per_join": 403,
"filtered": "10.00",
"cost_info": {
"read_cost": "388.15",
"eval_cost": "40.35",
"prefix_cost": "428.50",
"data_read_per_join": "97K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"attached_condition": "(`world`.`city`.`District` = 'Texas')"}}
1 row in set (0.00 sec)

20. explain_json_format_version variable
●Two versions available for EXPLAIN FORMAT=JSON.

●Version 2 reveals optimizer access paths.

●Ensures compatibility with upcoming MySQL Optimizer.

●Supports the JSON output format for EXPLAIN statements.

21. Version 1
Validation of the variable:

mysql> select @@explain_json_format_version;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
| 1 |
+-------------------------------+



mysql> select @@explain_json_format_version ;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
| 1 |
+-------------------------------+

22. mysql> Explain format = JSON select ID, Name, CountryCode, District,
Population from city where Population between 127800 and 137500\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "409.75"
},
"table": {
"table_name": "city",
"access_type": "ALL",
"rows_examined_per_scan": 4035,
"rows_produced_per_join": 448,
"filtered": "11.11",
"cost_info": {
"read_cost": "364.92",
"eval_cost": "44.83",
"prefix_cost": "409.75",
"data_read_per_join": "108K"
},
"used_columns": [
"ID", "Name","CountryCode", "District",
"Population"
],
"attached_condition": "(`world`.`city`.`Population` between 127800 and 137500)”} } }

23. Version 2
Setting the variable:




mysql> SET @@explain_json_format_version = 2;
Query OK, 0 rows affected (0.00 sec)

24. mysql> Explain format = JSON select ID, Name, CountryCode, District, Population
from city where Population between 127800 and 137500\G
*************************** 1. row ***************************
EXPLAIN: {
"query": "/* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS
`Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS
`District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where (`world`.`city`.`Population`
between 127800 and 137500)",
"inputs": [
{
"operation": "Table scan on city",
"table_name": "city",
"access_type": "table",
"schema_name": "world",
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"estimated_rows": 4035.0,
"estimated_total_cost": 409.75
}
],
"condition": "(city.Population between 127800 and 137500)",
"operation": "Filter: (city.Population between 127800 and 137500)",
"access_type": "filter",
"estimated_rows": 448.28851260244846,
"estimated_total_cost": 409.75
}

25. Demo

26. Consulting
Services
Consulting
Services
Connect with us !
Reach us at : [email protected]

27. Thank You!