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 …

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

Read the full story

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“, …

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.

Read the full story

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 …

Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting …