MySQL Cookbook Recipes for Developers

MySQL Cookbook 4th edition was released this summer. We are the book's authors and will show you how to "cook" MySQL. We will show you a few tasks with different priorities, such as JSON in MySQL for those who need flexibility, modern SQL for analytics, and Group Replication for high availability. We will also show how to write programs using JavaScript and Python languages, X DevAPI, and MySQL Shell. We will touch on some of the exciting features of MySQL Spatial Indexes and Geographical Data, Using a Full-Text Search, and more. We're hoping this talk will interest developers and administrators of MySQL.

Presenting the newly released MySQL Cookbook 4th edition to help developers and administrators to understand simple to complex recipes.

Transcript

1. MySQL Cookbook Recipes for Developers Sveta Smirnova, Percona Alkin Tezuysal, ChistaDATA
2. l • MySQL Support Engineer • Author MySQL Troubleshooting MySQL Cookbook, 4th Edition • JSON UDF functions • FILTER clause for MySQL • Speaker • Percona Live, OOW, Fosdem, DevConf, HighLoad... Sveta Smirnova
3. l SvetaSmirnova svetasmirnova @svetsmirnova svetsmirnova Let’s Connect!
4. • Audience: DBAs • They do not write queries • They tune • Server options • Indexes • Table structure Sveta the Speaker 4 ©2022 | Percona
5. • For developers • New topic for me → challenging • In past I worked as a developer MySQL Cookbook by O’Reilly 5 ©2022 | Percona
6. • For developers • New topic for me → challenging • In past I worked as a developer • I accepted MySQL Cookbook by O’Reilly 6 ©2022 | Percona
7. • Queries sql = "SELECT name, lastname " + "FROM my_table " + "WHERE id = " + my_id 15 years ago 7 ©2022 | Percona
8. • Placeholders sql = "SELECT name, lastname " + "FROM my_table " + "WHERE id = ?" 15 years ago 8 ©2022 | Percona
9. • ORM Speaker(name="Sveta", lastname="Smirnova").save() 15 years ago 9 ©2022 | Percona
10. • Nothing Was anything changed? 10 ©2022 | Percona
11. • Nothing • New MySQL APIs • Document Store support in MySQL • Object-oriented query language in MySQL X DevAPI Was anything changed? 11 ©2022 | Percona
12. Our Topics Today 12 ©2022 | Percona
13. mysql > SELECT ’Hello, world!’; +---------------+ | Hello, world! | +---------------+ | Hello, world! | +---------------+ 1 row in set (0,00 sec) MySQL CLI 13 ©2022 | Percona
14. • Your SQL code debugger • Tested by • Millions of users • MySQL developers • Hundreds of tests at every release • Model API for your query MySQL CLI 14 ©2022 | Percona
15. MySQL JS > print("Hello, world!") Hello, world! MySQL JS > \py Switching to Python mode... MySQL Py > print("Hello, world!") Hello, world! MySQL Py > \sql Switching to SQL mode... Commands end with ; MySQL SQL > SELECT ’Hello, world!’; +---------------+ | Hello, world! | +---------------+ | Hello, world! | +---------------+ 1 row in set (0.0003 sec) MySQL Shell 15 ©2022 | Percona
16. • New command-line client with X DevAPI support • SQL and Object-Oriented queries • MySQL Server administration • Utilities • Replication • InnoDB Cluster • Your own applications MySQL Shell 16 ©2022 | Percona
17. • Asynchronous code execution • Works with MySQL • As usual: by executing SQL • Querying tables as documents • Collections and documents support Data storage in JSON NoSQL-syntax, similar to MongoDB’s X DevAPI 17 ©2022 | Percona
18. Standard SQL SQL > SELECT thing, SUM(legs+arms) AS limbs -> FROM limbs GROUP BY thing -> HAVING limbs > 5; +-----------+-------+ | thing | limbs | +-----------+-------+ | armchair | 6 | | centipede | 99 | | insect | 6 | | squid | 10 | +-----------+-------+ 4 rows in set (0.0004 sec) Reading 18 ©2022 | Percona
19. X DevAPI for tables JS > session.getCurrentSchema(). -> getTable(’limbs’). -> select().groupBy(’thing’). -> having(’SUM(legs + arms) > 5’) -> +-----------+------+------+ | thing | legs | arms | +-----------+------+------+ | armchair | 4 | 2 | | centipede | 99 | 0 | | insect | 6 | 0 | | squid | 0 | 10 | +-----------+------+------+ 4 rows in set (0.0005 sec) Reading 19 ©2022 | Percona
20. Document Store Py > session.get_current_schema(). get_collection(’collectionLimbs’). find(’IFNULL(arms, 0) + IFNULL(legs, 0) > 5’) {"_id":"000061ed7c240000000000000002", "arms":0,"legs":6,"thing":"insect"} {"_id":"000061ed7c240000000000000003", "arms":10,"legs":0,"thing":"squid"} {"_id":"000061ed7c240000000000000005", "arms":0,"legs":99,"thing":"centipede"} {"_id":"000061ed7c240000000000000007", "arms":2,"legs":4,"thing":"armchair"} 4 documents in set (0.0004 sec) Reading 20 ©2022 | Percona
21. Tables and Document Store JS > session.getCurrentSchema(). -> getCollectionAsTable(’collectionLimbs’). -> select(’JSON_EXTRACT(doc, "$.thing") AS thing’, -> ’SUM(IFNULL(JSON_EXTRACT(doc, "$.arms"), 0) + -> IFNULL(JSON_EXTRACT(doc, "$.legs"), 0)) AS limbs’). -> groupBy(’thing’).having(’limbs > 5’) -> +-------------+-------+ | thing | limbs | +-------------+-------+ | "insect" | 6 | | "squid" | 10 | | "centipede" | 99 | | "armchair" | 6 | +-------------+-------+ 4 rows in set (0.0006 sec) Reading 21 ©2022 | Percona
22. SQL code in Python cursor = conn.cursor() cursor.execute("SELECT id, name, cats FROM profile") while True: row = cursor.fetchone() if row is None: break print(f"id: row[0], name: row[1], cats: row[2]") Working with Results 22 ©2022 | Percona
23. X DevAPI for tables result = session.get_schema(’cookbook’). get_table(’profile’). select(’id’, ’name’, ’cats’).execute() while True: row = result.fetch_one() if row is None: break print(f"id: row[0], name: row[1], cats: row[2]") Working with Results 23 ©2022 | Percona
24. Document Store result = session.get_schema(’cookbook’). get_collection(’collectionProfile’). find().execute() while True: doc = result.fetch_one() if doc is None: break print(f"id: doc[’id’], name: doc[’name’], cats: doc[’cats’]") Working with Results 24 ©2022 | Percona
25. Tables and Document Store result = session.get_schema(’cookbook’). get_collection_as_table(’collectionProfile’). select(’JSON_EXTRACT(doc, "$.id") AS id’). select(’JSON_EXTRACT(doc, "$.name") AS name’). select(’JSON_EXTRACT(doc, "$.cats") AS cats’).execute() while True: row = result.fetch_one() if row is None: break print(f"id: row[0], name: row[1], cats: row[2]") Working with Results 25 ©2022 | Percona
26. Standard SQL INSERT INTO limbs(thing, legs, arms) VALUES(’cat’, 2, 2); Changing Data 26 ©2022 | Percona
27. X DevAPI for tables JS > session.getSchema(’cookbook’). -> getTable(’limbs’). -> update(). -> set(’legs’, 4). -> set(’arms’, 0). -> where(’thing = "cat"’) -> Changing Data 27 ©2022 | Percona
28. Document Store Py > session.get_schema(’cookbook’). get_collection(’collectionLimbs’). add_or_replace_one(’00006002f065000000000000006b’, {"thing": "cat", "legs": 4, "arms": 0}) Changing Data 28 ©2022 | Percona
29. Tables and Document Store JS > session.getSchema(’cookbook’). -> getCollectionAsTable(’collectionLimbs’). -> delete(). -> where(’JSON_EXTRACT(doc, "$.thing") = "cat"’) -> Changing Data 29 ©2022 | Percona
30. Standard API: establishing connection conn_params = { "database": "test", "host": "localhost", "user": "sveta", "password": "", "charset": "cp1251" } conn = mysql.connector.connect(**conn_params) Character Encoding 30 ©2022 | Percona
31. Standard API: SET NAMES cursor.execute("SET NAMES utf8mb4") Character Encoding 31 ©2022 | Percona
32. • X DevAPI • Only utf8mb4 May not work with your connector! Character Encoding 32 ©2022 | Percona
33. • When was the longest trip per driver? +--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2014-07-30 | 152 | | 2 | Suzi | 2014-07-29 | 391 | | 3 | Henry | 2014-07-29 | 300 | | 4 | Henry | 2014-07-27 | 96 | | 5 | Ben | 2014-07-29 | 131 | | 6 | Henry | 2014-07-26 | 115 | | 7 | Suzi | 2014-08-02 | 502 | | 8 | Henry | 2014-08-01 | 197 | | 9 | Ben | 2014-08-02 | 79 | | 10 | Henry | 2014-07-30 | 203 | +--------+-------+------------+-------+ Generating Summaries 33 ©2022 | Percona
34. Naive solution mysql> SELECT name, trav_date, MAX(miles) AS ’longest trip’ -> FROM driver_log GROUP BY name; ERROR 1055 (42000): ’cookbook.driver_log.trav_date’ isn’t in GROUP BY mysql> SET sql_mode=”; Query OK, 0 rows affected (0,00 sec) mysq> SELECT name, trav_date, MAX(miles) AS ’longest trip’ -> FROM driver_log GROUP BY name; +-------+------------+--------------+ | name | trav_date | longest trip | +-------+------------+--------------+ | Ben | 2014-07-30 | 152 | | Suzi | 2014-07-29 | 502 | | Henry | 2014-07-29 | 300 | +-------+------------+--------------+ 3 rows in set (0,00 sec) Generating Summaries 34 ©2022 | Percona
35. Legacy solution mysql> CREATE TEMPORARY TABLE t -> SELECT name, MAX(miles) AS miles -> FROM driver_log GROUP BY name; mysql> SELECT d.name, d.trav_date, d.miles AS ’longest trip’ -> FROM driver_log AS d INNER JOIN t USING (name, miles) -> ORDER BY name; +-------+------------+--------------+ | name | trav_date | longest trip | +-------+------------+--------------+ | Ben | 2014-07-30 | 152 | | Henry | 2014-07-29 | 300 | | Suzi | 2014-08-02 | 502 | +-------+------------+--------------+ mysql> DROP TABLE t; Generating Summaries 35 ©2022 | Percona
36. Common Table Expression (CTE) mysql> WITH t AS -> (SELECT name, MAX(miles) AS miles -> FROM driver_log GROUP BY name) -> SELECT d.name, d.trav_date, d.miles AS ’longest trip’ -> FROM driver_log AS d INNER JOIN t USING (name, miles) -> ORDER BY name; +-------+------------+--------------+ | name | trav_date | longest trip | +-------+------------+--------------+ | Ben | 2014-07-30 | 152 | | Henry | 2014-07-29 | 300 | | Suzi | 2014-08-02 | 502 | +-------+------------+--------------+ 3 rows in set (0.01 sec) Generating Summaries 36 ©2022 | Percona
37. CHECK Constraints mysql> ALTER TABLE patients ADD CONSTRAINT date_check -> CHECK((date_departed IS NULL) OR -> (date_departed >= date_arrived)); mysql> INSERT INTO patients (national_id, name, surname, -> gender, age, diagnosis, date_arrived, date_departed) -> VALUES(’34GD429520’, ’John’, ’Doe’, ’M’, 45, -> ’Data Phobia’, ’2020-07-20’, ’2020-05-31’); ERROR 3819 (HY000): Check constraint ’date_check’ is violated. Validation and Formatting 37 ©2022 | Percona
38. JSON Schema for collections JS > schema={ -> "$schema": "http://json-schema.org/draft-07/schema", -> "id": "http://example.com/cookbook.json", -> "type": "object", -> "description": "Table limbs as a collection", -> "properties": { -> "thing": {"type": "string"}, -> "legs": { -> "anyOf": [{"type": "number"},{"type": "null"}], -> "default": 0 -> }, -> "arms": { -> "anyOf": [{"type": "number"},{"type": "null"}], -> "default": 0 -> }}, -> "required": ["thing","legs","arms"] } Validation and Formatting 38 ©2022 | Percona
39. JS > collectionLimbs=session.getCurrentSchema(). -> createCollection(’collectionLimbs’, -> {"validation": {"level": "strict", "schema": schema}}) -> Validation and Formatting 39 ©2022 | Percona
40. Enumerating result mysql> SELECT -> ROW_NUMBER() OVER win AS turn, -> first_name, last_name FROM name -> WINDOW win -> AS (ORDER BY RAND()); +------+------------+-----------+ | turn | first_name | last_name | +------+------------+-----------+ | 1 | Devon | White | | 2 | Kevin | Brown | | 3 | Rondell | White | | 4 | Vida | Blue | | 5 | Pete | Gray | +------+------------+-----------+ 5 rows in set (0.00 sec) Sequences 40 ©2022 | Percona
41. Several sequences in one query mysql> WITH RECURSIVE sequences(id, geo, random) AS -> (SELECT 1, 3, FLOOR(1+RAND()*5) -> UNION ALL -> SELECT id + 1, geo * 4, FLOOR(1+RAND()*5) FROM sequences WHERE id < 5) -> SELECT * FROM sequences; +------+------+--------+ | id | geo | random | +------+------+--------+ | 1 | 3 | 4 | | 2 | 12 | 4 | | 3 | 48 | 2 | | 4 | 192 | 2 | | 5 | 768 | 3 | +------+------+--------+ 5 rows in set (0.00 sec) Sequences 41 ©2022 | Percona
42. How many drivers on the road? mysql> SELECT trav_date, COUNT(trav_date) AS drivers -> FROM driver_log GROUP BY trav_date ORDER BY trav_date; +------------+---------+ | trav_date | drivers | +------------+---------+ | 2014-07-26 | 1 | | 2014-07-27 | 1 | | 2014-07-29 | 3 | | 2014-07-30 | 2 | | 2014-08-01 | 1 | | 2014-08-02 | 2 | +------------+---------+ Joins and Subqueries 42 ©2022 | Percona
43. Missed dates mysql> CREATE TABLE dates (d DATE); -> INSERT INTO dates (d) -> VALUES(’2014-07-26’),(’2014-07-27’),(’2014-07-28’), -> (’2014-07-29’),(’2014-07-30’),(’2014-07-31’), -> (’2014-08-01’),(’2014-08-02’); Joins and Subqueries 43 ©2022 | Percona
44. When drivers have rest? mysql> SELECT dates.d -> FROM dates LEFT JOIN driver_log -> ON dates.d = driver_log.trav_date -> WHERE driver_log.trav_date IS NULL; +------------+ | d | +------------+ | 2014-07-28 | | 2014-07-31 | +------------+ Joins and Subqueries 44 ©2022 | Percona
45. CTE: in one query WITH RECURSIVE dates (d) AS ( SELECT ’2014-07-26’ UNION ALL SELECT d + INTERVAL 1 day FROM dates WHERE d < ’2014-08-02’) SELECT dates.d, COUNT(driver_log.trav_date) AS drivers FROM dates LEFT JOIN driver_log ON dates.d = driver_log.trav_date GROUP BY d ORDER BY d; Joins and Subqueries 45 ©2022 | Percona
46. Ranking: legacy way mysql> SET @rownum := 0; Query OK, 0 rows affected (0,00 sec) mysql> SELECT @rownum := @rownum + 1 AS ‘rank‘, score FROM ranks ORDER BY score DESC; +------+-------+ | rank | score | +------+-------+ | 1 | 5 | | 2 | 4 | | 3 | 4 | | 4 | 3 | | 5 | 2 | | 6 | 2 | | 7 | 2 | | 8 | 1 | +------+-------+ 8 rows in set, 1 warning (0,00 sec) Statistics 46 ©2022 | Percona
47. The issue! mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: ’SET variable=expression, ...’, or ’SELECT expression(s) INTO variables(s)’. 1 row in set (0,00 sec) Statistics 47 ©2022 | Percona
48. Ranking: Window Functions mysql> SELECT ROW_NUMBER() OVER win AS ’rank’, score -> FROM ranks WINDOW win AS (ORDER BY score DESC); +------+-------+ | rank | score | +------+-------+ | 1 | 5 | | 2 | 4 | | 3 | 4 | | 4 | 3 | | 5 | 2 | | 6 | 2 | | 7 | 2 | | 8 | 1 | +------+-------+ 8 rows in set (0,00 sec) Statistics 48 ©2022 | Percona
49. Repeating results mysql> SELECT ROW_NUMBER() OVER win AS ’row’, -> RANK() OVER win AS ’rank’, -> score FROM ranks WINDOW win AS (ORDER BY score DESC); +------+------+-------+ | row | rank | score | +------+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 4 | | 3 | 2 | 4 | | 4 | 4 | 3 | | 5 | 5 | 2 | | 6 | 5 | 2 | | 7 | 5 | 2 | | 8 | 8 | 1 | +------+------+-------+ Statistics 49 ©2022 | Percona
50. Duplicate users mysql> WITH tmp AS ( -> SELECT COUNT(*) AS count, last_name, first_name -> FROM catalog_list GROUP BY last_name, first_name HAVING count > 1) -> SELECT catalog_list.* -> FROM tmp INNER JOIN catalog_list USING (last_name, first_name) -> ORDER BY last_name, first_name; +-----------+------------+----------------------+ | last_name | first_name | street | +-----------+------------+----------------------+ | Baxter | Wallace | 57 3rd Ave. | | BAXTER | WALLACE | 57 3rd Ave. | | Baxter | Wallace | 57 3rd Ave., Apt 102 | | Pinter | Marlene | 9 Sunset Trail | | Pinter | Marlene | 9 Sunset Trail | +-----------+------------+----------------------+ 5 rows in set (0,00 sec) Duplicates 50 ©2022 | Percona
51. • Data type JSON • Compact storage • In-place update On the source and replica binlog_row_value_options=PARTIAL_JSON • Operators -> and ->> • Functions Search, pattern support Update Validation, including JSON schema Conversion Documents join JSON 51 ©2022 | Percona
52. • JSON Path for queries Names of book authors mysql> SELECT JSON_EXTRACT(author, ’$.name’) AS author -> FROM book_authors; +---------+ | author | +---------+ | "Paul" | | "Alkin" | | "Sveta" | +---------+ 3 rows in set (0,00 sec) JSON 52 ©2022 | Percona
53. • JSON Path for queries Names of book authors mysql> SELECT author->’$.name’ AS author -> FROM book_authors; +---------+ | author | +---------+ | "Paul" | | "Alkin" | | "Sveta" | +---------+ 3 rows in set (0,00 sec) JSON 53 ©2022 | Percona
54. • JSON Path for queries Removing quotes mysql> SELECT JSON_UNQUOTE( -> JSON_EXTRACT(author, ’$.name’) -> ) AS author FROM book_authors; +--------+ | author | +--------+ | Paul | | Alkin | | Sveta | +--------+ 3 rows in set (0,00 sec) JSON 54 ©2022 | Percona
55. • JSON Path for queries Removing quotes mysql> SELECT author-»’$.name’ AS author -> FROM book_authors; +--------+ | author | +--------+ | Paul | | Alkin | | Sveta | +--------+ 3 rows in set (0,00 sec) JSON 55 ©2022 | Percona
56. • JSON Path for queries First and last books mysql> SELECT CONCAT(author-»’$.name’, ’ ’, author-»’$.lastname’) AS author, -> author-»’$.books[0]’ AS ‘First Book‘, author-»’$.books[last]’ AS ‘Last Book‘ -> FROM book_authors\G ************************ 1. row ************************ author: Paul DuBois First Book: Software Portability with imake: ... Last Book: MySQL (Developer’s Library) ************************ 2. row ************************ author: Alkin Tezuysal First Book: MySQL Cookbook Last Book: MySQL Cookbook ************************ 3. row ************************ author: Sveta Smirnova First Book: MySQL Troubleshooting Last Book: MySQL Cookbook JSON 56 ©2022 | Percona
57. Indexes mysql> ALTER TABLE book_authors -> ADD COLUMN lastname VARCHAR(255) -> GENERATED ALWAYS AS -> (JSON_UNQUOTE(JSON_EXTRACT(author, ’$.lastname’))); mysql> ALTER TABLE book_authors -> ADD COLUMN name VARCHAR(255) -> GENERATED ALWAYS AS (author-»’$.name’); mysql> CREATE INDEX author_name -> ON book_authors(lastname, name); JSON 57 ©2022 | Percona
58. l Alkin will continue Thank you!
59. © 2022 | Percona Let’s get connected with Alkin first Alkin Tezuysal - EVP - Global Services @chistadata • Linkedin : https://www.linkedin.com/in/askdba/ • Twitter: https://twitter.com/ask_dba Open Source Database Evangelist • Previously PlanetScale, Percona and Pythian as Technical Manager, SRE, DBA • Previously Enterprise DBA , Informix, Oracle, DB2 , SQL Server Author, Speaker, Mentor, and Coach @ChistaDATA Inc. 2022 @ask_dba
60. © 2022 | Percona Also… Born to Sail, Forced to Work @ChistaDATA Inc. 2022 @ask_dba
61. © 2022 | Percona About ChistaDATA Inc. Founded in 2021 by Shiv Iyer - CEO and Principal Has received 3M USD seed investment ( 2021) Focusing on ClickHouse infrastructure engineering and performance operations What’s ClickHouse anyway? Services and Products around dedicated Managed Services, Support and Consulting We’re hiring globally DBAs, SREs and DevOps Engineers @ChistaDATA Inc. 2022 @ask_dba
62. © 2022 | Percona About ClickHouse Columnar Storage SQL Compatible Open Source (Apache 2.0) Shared Nothing Architecture Parallel Execution Rich in Aggregate Functions Super fast for Analytics workload @ChistaDATA Inc. 2022 @ask_dba
63. © 2022 | Percona Agenda Query Performance and Indexes • Indexing basics • Using a Full-Text Search • MySQL Spatial Indexes and Geographical Data • Using Histograms @ChistaDATA Inc. 2022 @ask_dba
64. © 2022 | Percona Indexes in MySQL • Table Scan • Tree Traversal • Leaf Nodes • B-tree Structure • Hash Indexing • Spatial • Full-text • Histograms @ask_dba @ask_dba
65. © 2022 | Percona B-tree - is a self-balancing tree data structure. @ask_dba
66. © 2022 | Percona Creating a Surrogate Primary Key Problem A table without a primary key is not performant enough Solution Add a primary key to all InnoDB tables. @ask_dba
67. © 2022 | Percona mysql> ALTER TABLE `top_names` -> ADD COLUMN `names_id` int unsigned NOT NULL -> AUTO_INCREMENT PRIMARY KEY FIRST; Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0 Add Primary Key @ask_dba
68. © 2022 | Percona Deciding When a Query Can Use an Index Problem Your table has an index, but queries are still slow. Solution Check the query plan using EXPLAIN to make sure the right index has been used. @ask_dba
69. © 2022 | Percona Use of Explain mysql> EXPLAIN SELECT name_rank,top_name,name_count FROM top_names -> WHERE name_rank < 10 ORDER BY name_count \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: top_names partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 161604 filtered: 33.33 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) @ask_dba
70. © 2022 | Percona Deciding the Order for Multiple Column Indexes Use covering index mysql> SELECT name_rank,top_name,name_count FROM top_names -> WHERE name_rank < 10 ORDER BY name_count; mysql> CREATE INDEX idx_name_rank_count ON top_names(name_count,name_rank); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT name_rank,top_name,name_count FROM top_names -> WHERE name_rank < 10 ORDER BY name_count; *************************** 1. row *************************** @ask_dba
71. © 2022 | Percona Using Full Text Indexes Problem You want to take advantage of a keyword search, but queries on text fields are slow. Solution Use FULLTEXT indexes for full-text searches. @ask_dba
72. © 2022 | Percona FULLTEXT in action FULLTEXT indexes have two other conditions: 1. They can be used only for CHAR, VARCHAR, or TEXT columns. 2. They can be used only when there is a MATCH() or AGAINST() clause in a SELECT statement. @ask_dba
73. © 2022 | Percona Fulltext Index Modes • Natural language mode (default) is the search mode for simple phrases. SELECT top_name,name_rank FROM top_names WHERE MATCH(top_name) AGAINST("ANDREW" IN NATURAL LANGUAGE MODE); @ask_dba
74. © 2022 | Percona Fulltext Index Modes • Boolean mode is for using Boolean operators in search mode. SELECT top_name,name_rank FROM top_names WHERE MATCH(top_name) AGAINST("+ANDREW +ANDY -ANN" IN BOOLEAN MODE); @ask_dba
75. © 2022 | Percona Fulltext Index Modes • Query expansion mode is the search mode for similar or related values in a search expression. In short, this mode will return relevant matches against a searched Keyword: SELECT top_name,name_rank FROM top_names WHERE MATCH(top_name) AGAINST("ANDY" WITH QUERY EXPANSION); @ask_dba
76. © 2022 | Percona The optimizer will choose the none FULLTEXT index mysql> EXPLAIN SELECT top_name,name_rank FROM top_names -> WHERE top_name="ANDREW" \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: top_names partitions: NULL type: ref possible_keys: idx_top_name,idx_desc_01,idx_desc_02 key: idx_top_name key_len: 103 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) @ask_dba
77. © 2022 | Percona Create the FULLTEXT index mysql> CREATE FULLTEXT INDEX idx_fulltext ON top_names(top_name); Query OK, 0 rows affected, 1 warning (1.94 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> EXPLAIN SELECT top_name,name_rank FROM top_names -> WHERE top_name="ANDREW" ÄG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: top_names partitions: NULL type: ref possible_keys: idx_top_name,idx_desc_01,idx_desc_02,idx_fulltext key: idx_top_name key_len: 103 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) @ask_dba
78. © 2022 | Percona Utilizing Spatial Indexes and Geographical Data Problem You want to store and query geographic coordinates effectively. Solution Use MySQL’s improved Spatial Reference System. @ask_dba
79. © 2022 | Percona MySQL’s improved Spatial Reference System Creating geometries in various formats Converting geometries between formats Accessing qualitative and quantitative properties of geometry Describing relations between two geometries Creating new geometries from existing ones @ask_dba
80. © 2022 | Percona Different variations of geographic data references mysql> SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS -> WHERE SRS_ID=4326 OR SRS_ID=3857 ORDER BY SRS_ID DESC \G *************************** 1. row *************************** SRS_NAME: WGS 84 SRS_ID: 4326 ORGANIZATION: EPSG ORGANIZATION_COORDSYS_ID: 4326 DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984", SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]], AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]] DESCRIPTION: NULL … @ask_dba
81. © 2022 | Percona SRS_ID 4326 represents map projections used in Google Maps mysql> CREATE TABLE poi -> ( poi_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> position POINT NOT NULL SRID 4326, name VARCHAR(200)); Query OK, 0 rows affected (0.02 sec) @ask_dba
82. © 2022 | Percona Insert GeoSpatial data with ST_GeomFromText() mysql> INSERT INTO poi VALUES (1, ST_GeomFromText('POINT(41.0211 29.0041)', 4326), -> 'Maiden's Tower'); Query OK, 1 row affected (0.00 sec) msyql> INSERT INTO poi VALUES (2, ST_GeomFromText('POINT(41.0256 28.9742)', 4326), -> 'Galata Tower'); Query OK, 1 row affected (0.00 sec) @ask_dba
83. © 2022 | Percona Create SPATIAL index mysql> CREATE SPATIAL INDEX position ON poi (position); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 @ask_dba
84. © 2022 | Percona Measure the distance between coordinates ST_Distance() mysql> SELECT ST_AsText(position) FROM poi WHERE poi_id = 1 INTO @tower1; Query OK, 1 row affected (0.00 sec) mysql> SELECT ST_AsText(position) FROM poi WHERE poi_id = 2 INTO @tower2; Query OK, 1 row affected (0.00 sec) mysql> SELECT ST_Distance(ST_GeomFromText(@tower1, 4326), -> ST_GeomFromText(@tower2, 4326)) AS distance; +--------------------+ | distance | +--------------------+ | 2563.9276036976544 | +--------------------+ 1 row in set (0.00 sec) @ask_dba
85. © 2022 | Percona Improved Earth’s spherical shape with ST_Distance_Sphere () mysql> SELECT ST_Distance_Sphere(ST_GeomFromText(@tower1, 4326), -> ST_GeomFromText(@tower2, 4326)) AS dist; +--------------------+ | dist | +--------------------+ | 2557.7412439442496 | +--------------------+ 1 row in set (0.00 sec) @ask_dba
86. © 2022 | Percona Set a polygon with coordinates mysql> SET @poly := ST_GeomFromText ( 'POLYGON(( 41.104897239651905 28.876082545638166, -> 41.05727989444261 29.183699733138166, -> 40.90384226781947 29.137007838606916, -> 40.94119778455447 28.865096217513166, -> 41.104897239651905 28.876082545638166))', 4326); @ask_dba @ask_dba
87. © 2022 | Percona Find two towers from Istanbul using SPATIAL index ST_Within() mysql> SELECT poi_id, name, ST_AsText(`position`) -> AS `towers` FROM poi WHERE ST_Within( `position`, @poly) ; +--------+----------------+------------------------+ | poi_id | name | towers | +--------+----------------+------------------------+ | 1 | Maiden's Tower | POINT(41.0211 29.0041) | | 2 | Galata Tower | POINT(41.0256 28.9742) | +--------+----------------+------------------------+ 2 rows in set (0.00 sec) @ask_dba @ask_dba
88. © 2022 | Percona Creating and Using Histograms Problem You want to join two or more tables, but MySQL’s optimizer does not choose the right query plan. Solution Use optimizer histograms to aid decision making. @ask_dba @ask_dba
89. © 2022 | Percona MySQL Optimizer Histograms Lightweight data structures that store information about how many unique values exist in each data bucket. mysql> ANALYZE TABLE histograms UPDATE HISTOGRAM ON f1 \G *************************** 1. row *************************** Table: cookbook.histograms Op: histogram Msg_type: status Msg_text: Histogram statistics created for column 'f1'. 1 row in set (0,01 sec) @ask_dba @ask_dba
90. © 2022 | Percona Found in Information Schema mysql> SELECT * FROM information_schema.column_statistics -> WHERE table_name='histograms'ÄG *************************** 1. row *************************** SCHEMA_NAME: cookbook TABLE_NAME: histograms COLUMN_NAME: f1 HISTOGRAM: {"buckets": [[1, 0.16666666666666666], [2, 0.5], [3, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2021-05-23 17:29:46.595599", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100} 1 row in set (0,00 sec) @ask_dba
91. © 2022 | Percona Query before Histogram mysql> grep filtered PAGER set to 'grep filtered' mysql> EXPLAIN SELECT * FROM histograms WHERE f1=1 \G filtered: 16.67 1 row in set, 1 warning (0,00 sec) mysql> EXPLAIN SELECT * FROM histograms WHERE f1=2 \G filtered: 16.67 1 row in set, 1 warning (0,00 sec) mysql> EXPLAIN SELECT * FROM histograms WHERE f1=3 \G filtered: 16.67 1 row in set, 1 warning (0,00 sec) @ask_dba
92. © 2022 | Percona Query after Histogram mysql> grep filtered PAGER set to 'grep filtered' mysql> EXPLAIN SELECT * FROM histograms WHERE f1=1 \G filtered: 16.67 1 row in set, 1 warning (0,00 sec) mysql> EXPLAIN SELECT * FROM histograms WHERE f1=2 \G filtered: 33.33 1 row in set, 1 warning (0,00 sec) mysql> EXPLAIN SELECT * FROM histograms WHERE f1=3 \G filtered: 50.00 1 row in set, 1 warning (0,00 sec) @ask_dba
93. © 2022 | Percona Writing Performant Queries Problem You want to write efficient queries. Solution Study how MySQL accesses data, and adjust your queries to help MySQL perform its job faster. @ask_dba
94. © 2022 | Percona MySQL’s cost-based optimizer 1. Find the optimal method. 2. Check if the access method is useful. 3. Estimate the cost of using the access method. 4. Select the lowest-cost access method possible. @ask_dba
95. © 2022 | Percona Query execution that MySQL chooses 1. Table scan 2. Index scan 3. Index lookup 4. Range scan 5. Index merge 6. Loose index scan @ask_dba
96. © 2022 | Percona Conclusion - Slow Queries Low cardinality Large datasets Multiple index traversal Nonleading column lookup Data type mismatch Character set collation mismatch Suffix lookup Index as argument Stale statistics MySQL bug @ask_dba
97. © 2022 | Percona @ChistaDATA Inc. 2022 @ask_dba
98. Thank you!