Home

MySQL Query Attributes

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionQuery attributes have in a long time been used to add metadata to queries to provide additional context such as where in the application the query was generated. The traditional way to do this is to add a comment at the start of the query with the attributes. Since the MySQL parser ignores the comment, it does not change the semantics of the query, but various tools can extract the comments. This post looks into the reasons for using query attributes, how it is done using a comment, and the new component for MySQL 8.0.23 that provides native support for query attributes. Why Query Attributes?

Read More »

New Book: MySQL Concurrency

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesIn March 2020, my book MySQL 8 Query Performance Tuning (Apress) was released. That book with its more than 900 pages in 27 chapters covered a wide range of topics from executing benchmarks with sysbench over indexes and the optimizer to query rewriting, replication, and caching. Some time later my editor Jonathan Gennick suggested to take a limited set of the topics and go into more detail. We settled on locks and transactions which together have become MySQL Concurrency. If you have already read MySQL 8 Query Performance Tuning, you may wonder what the new book offers compared

Read More »

The MySQL X DevApi: Working with NULL Values

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputThere was an excellent question from Francis on my MySQL X DevAPI tutorial on how to work with NULL values: Is it possible to store an attribute with the null value, and how to find it (xxx is null ? It may sound like a trivial question, but the answer is not all that simple, so instead of just replying directly to the comment, I think it is worth covering the subject of

Read More »

I Am Speaking at Oracle Developer Live – MySQL 2020

It is a year of online conferences and next week, the time has come to Oracle Developer Live for MySQL. It consists of a keynote at the start of the day and two tracks at the rest of the day. The event takes place in two rounds: On 13 October the event takes place during American business hours, and on 15 October it happens during European, African, Middle Eastern, and Asian-Pacific business hours/evening. I am honoured to have been given the opportunity to speak about MySQL performance tuning. On 13 October, I will speak at 12:05pm Pacific Time and on 15 October at 10:05 Central European Time/1:35pm Indian time/7:05pm Australian Eastern Daylight Time. A teaser: you will learn about the

Read More »

Happy Birthday MySQL

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputToday 23 May 2020, it is 25 years since the first release of MySQL. So, I would like to take the opportunity to wish MySQL – and Sakila – a happy birthday. My own MySQL journey started in 2006 when I at a job interview was told that if I got the job, I would need to learn MySQL before starting. Since the job involved PHP coding, I got the book Web Database

Read More »

MySQL Compressed Binary Logs

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionOn a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you

Read More »

Apress Blog: MySQL Performance Tuning Best Practices

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionTo celebrate the publishing of my new book MySQL 8 Query Performance Tuning, the Apress team invited me (thanks Jonathan and Liz) to write a post for the Apress blog. I decided to write about my top six best practices: Be wary of best practices Monitor Work methodically Consider the full stack Make small, incremental changes Understand the change Yes, my first best practice is to be wary

Read More »

MySQL 8.0.20: Index-Level Optimizer Hints

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?MySQL introduced optimizer hints in version 5.7 and greatly extended the feature in MySQL 8. One thing that has been missing though is the ability to specify index hints using the syntax of optimizer hints. This has been improved of in MySQL 8.0.20 with the introduction of index-level optimizer hints for the FORCE and IGNORE versions of the index hints. This blog

Read More »

New Book: MySQL 8 Query Performance Tuning

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesI have over the last few years been fortunate to have two books published through Apress, Pro MySQL NDB Cluster which I wrote together with Mikiya Okuno and MySQL Connector/Python Revealed. With the release of MySQL 8 around a

Read More »

Back Up MySQL View Definitions

Table Of Contents Why Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesIf you want to back up your table and views, stored procedures, or stored function definitions, you can use mysqldump or mysqlpump to export the schema without the data. However, if you just want the views you need to

Read More »