If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the table, find duplicate indexes, and find unused indexes. As promised, they are below.

— Find tables without PK
SELECT t.table_schema,t.table_name,t.engine
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema=c.table_schema
AND t.table_name=c.table_name
WHERE t.table_schema NOT IN (‘mysql’, ‘information_schema’, ‘sys’, ‘performance_schema’)
AND t.table_type = ‘BASE TABLE’
GROUP BY t.table_schema,t.table_name, t.engine
HAVING …

MySQL gave us the JSON data type back in mid-2015 with the release of MySQL 5.7.8. Since then, it has been used as a way to escape rigid column definitions and store JSON documents of all shapes and sizes: audit logs, configuration settings, 3rd party payloads, user-defined fields, and more. Although MySQL gives us functions for reading and writing JSON data, you’ll quickly discover something that is conspicuously missing: the ability to directly index your JSON columns. In other databases, the best way to directly index a JSON column is usually through a type of index known as a Generalized Inverted Index, or GIN for short. Since MySQL doesn’t offer GIN indexes, we’re unable to directly index an entire stored JSON document. All is not lost though, because MySQL does give us a way to indirectly index parts of our stored JSON documents. Depending on the version of MySQL that you're using, you have two options for indexing JSON. In MySQL 5.7 you …

Learn how to index JSON in MySQL with generated columns and functional indexes.

I recently had the chance to read in preview Bill Karwin‘s new book: SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

This book is a rework of Bill’s best seller from 2010. The new book can be considered as the second edition and is updated with Bill’s latest observations about common mistakes.

The author took in consideration the feedback about the first edition.

This new edition is full of information for beginners but also for experts who will appreciate to see how antipatterns are solved and most of the time surely propose different approaches.

Each antipatterns are identified by their names like “jaywalking“, …

Overview Ever find yourself building a database only to start questioning what data types you should use for a specific column? In this entry of the MySQL data types series, we’ll explore the various ways you can save strings and text to a database to help demystify the options you have as a developer, starting with VARCHAR and CHAR. VARCHAR vs CHAR VARCHAR is probably the most widely used data type for strings. It stores a string of variable length up to a maximum of 65,535 characters. When creating a VARCHAR field, you’ll be able to specify the maxmimum number of characters the field will accept using the VARCHAR(n) format, where n is the maximum number of characters to be stored. Due to the fact that is is variable length, it will only allocate enough disk space to store the contents of the string, not the full length of the contents passed in. VARCHAR also allocates a little bit of extra space with each value stored. Depending on the space …

In this entry of the series we explore using VARCHAR and CHAR data types in your database and give some pointers on which type is best to use and when.

The subtitle of this post could have been “Review of an awesome MySQL event”.

I’ve been invited by the MySQL Italian team to deliver a session about MySQL Shell during the MySQL Day in Roma. This was the very first in person event by MySQL Italy since the pandemic.

The amount of attendees was large and we could feel the emotion and the enthusiasm of the audience. Everybody enjoyed to finally be together again and discus about MySQL.

The day started with a MySQL Overview by Andra Cazacu. She was very emotionalized to see how the room was packed.

I was really great to see all those people smiling, I could feel that people were very happy to be present at this event.

The Show Runner

After Andra, it was already the turn of the Italian Star: “il grande Marco Carlessi” !

Marco presented the latest news in MySQL:

• Multi Factor Authentication
• New …

I’ve had this list on a post-it note on my monitor for all of 2022. I figured it was time to write it down, and reuse the space.

In summary, AWS suffers from the same problem that almost every other product does. It sacrifices improved security for backward compatibility of functionality. IMO this is not in the best practices of a data ecosystem that is under constant attack.

• Storage should be encrypted by default. When you launch an RDS cluster its storage is not encrypted. This goes against their own AWS Well-Architected Framework Section 2 – Security.
• Plain text passwords. To launch a cluster you must specify a password in plain text on the command line, again not security best practice. At least change this to using a known secret from AWS secrets manager.
• TLS for administrative accounts should be the only option. The root user should only be REQUIRE SSL (MySQL syntax).
• Expanding on …

in this tutorial, we’ll learn How to enable MySQL Workbench dark theme on Windows and Linux. Only macOS and Linux support dark themes in MySQL Workbench. This tutorial will help you enable the dark theme on MySQL Workbench for Windows and Linux. How To Enable MySQL Workbench dark theme on Windows Only versions 8.0.26 or […]

The post Set Dark Theme on MySQL Workbench appeared first on Phpflow.com.

Where I work, we are using MySQL in a scale-out configuration to handle our database needs.

That means, you write to a primary server, but reads generally go to a replica database further down in a replication tree.

A number of additional requirements that should not concern you as a developer make it a little bit more elaborate than a simple “primary and a number of replicas” configuration. But the gist of all that is:

• there is always a read-copy of the database very close to your application, latency wise
• there are always sufficient copies of the data around so that we can afford to run our databases on unraided local storage.

The nature of our databases is such, that we drown all data reads with sufficient memory, where ever that is possible. Our databases are Memory Engines, when it comes to reads.

I joke about that:

You, too, can be a successful database …