One of the most frustrating experiences when dealing with databases is when you've designed the perfect index, but MySQL still doesn't use it. There are several reasons why this could be the case, and in this article, we'll explore some of the most common ones. Throughout this article, we'll be working with a very simple people table that looks like this:CREATE TABLE `people` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `state` char(2) NOT NULL, PRIMARY KEY (`id`), KEY `first_name` (`first_name`), KEY `state` (`state`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

We'll be adding and dropping keys throughout to show different scenarios, but this is a good starting place. Determining what index is being used Before you can determine why your index isn't being used, you must first determine that your index isn't being used. You can run an EXPLAIN on …

There are several reasons why MySQL might not consider your index, and in this article we’ll explore some of the most common ones.

OCI MySQL Heatwave allows customers to use OCI user accounts to authenticate using authentication_oci plugin. Up to MySQL 8.0.32, this required an API Key pair. Starting with MySQL 8.0.33, the plugin also supports IAM Security Tokens to authenticate a user generated through OCI CLI.

Sometimes crucial data sharing is avoided because of compliance rules, organizational policies, or numerous security concerns. The common use cases involve sharing pt-mysql-summary, pt-stalk, and other OS-related details to assist Support Engineers or any other third-party team troubleshoot database-related issues.

In this context, pt-secure-collect is a very important utility from Percona, which helps capture the required information securely and also provides aid in masking the existing information.

Pt-secure-collect helps in collecting, sanitizing, and encrypting data from various sources. By default, this utility collects the output with the help of pt-stalk, pt-summary, and pt-mysql-summary.

Let’s see how this tool …

Default settings can help you get started quickly – but they can also cost you performance and a higher cloud bill at the end of the month. Want to save money on your AWS RDS bill? I’ll show you some MySQL settings to tune to get better performance, and cost savings, with AWS RDS.

Recently I was engaged in a MySQL Performance Audit for a customer to help troubleshoot performance issues that led to downtime during periods of high traffic on their AWS RDS MySQL instances. During heavy loads, they would see messages about their InnoDB settings in the error logs:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4460ms. The settings might not be optimal. (flushed=140, during the time.)

This message is normally a side effect of a storage subsystem that is not capable of keeping up with the number of writes (e.g., IOPs) required by MySQL. This is …

With the wide range of frameworks and CMSs, someone can always find their way and explore their interests using the LAMP stack. Welcome to this week’s OpenLampTech newsletter and content. Thanks for reading!

Custom WooCommerce and Shopify Solutions

Discover useful WooCommerce and Shopify custom solutions for your online store today at affordable prices!

Learn More

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #76, we have some fantastic reads …

In the ever-evolving world of database management systems, SQL Server and MySQL stand as two of the most popular and widely-used solutions. Both platforms offer robust features and reliable performance, but which one is the right choice for your needs?

The post SQL Server vs MySQL: Difference, Performance, and Features appeared first on Devart Blog.

Learn how to build a Rust API using Rocket, Diesel, and a MySQL database hosted on PlanetScale.

Learn how to build a Rust API using Rocket, Diesel, and a MySQL database hosted on PlanetScale.

We have been talking about different MySQL operators in their guises for quite a while now: along with WHERE condition or SELECT statement, you name it. Now, the time to shine has come for the LIKE conditional operator used to search for patterns in strings with the help of wildcard characters. In this article, we will provide the basic syntax for using LIKE, along with illustrated examples of how to apply it in queries using one of the best MySQL IDEs on the market — dbForge Studio for MySQL.

The post MySQL LIKE Operator: 7 Examples and Best Practices appeared first on Devart Blog.