Histograms were introduced with MySQL 8.0 and are a valuable way of speeding up queries.  The MySQL optimizer assumes that data in a column has evenly distributed values. Even distribution of data probably does not reflect much of the data sitting right now in your database.  

    The optimizer wants to find the most efficient way to return the data requested in a query.  If it has poor information on that data, then the optimizer will make a 'guesstimate' that will will result in a query plan that will not perform well.  But if the optimizer has good information, in this case provided by a histogram, then it can produce a better query plan.

    In the following example a able is filled with data that is not evenly distributed.  In the histogram image following, the data is represented in what looks like a rollercoaster side view. 
…

The article provides an overview of the backup types available in MySQL and describes how-to examples of using the mysqldump command-line utility to take a backup of the database, tables, data, or schema and to restore the MySQL database. In addition, you can view how to generate a database backup with MySQL Workbench and how […]

The post Different Ways to Back up MySQL Databases and Tables appeared first on Devart Blog.

How to prevent connections to a MySQL server using a TLS certificate that has been revoked by a non-root certificate authority.

On behalf of the Vitess maintainers, I am pleased to announce the general availability of Vitess 12. Major Themes # In this release, Vitess Maintainers have made significant progress in several areas, including Gen4 planner, VTAdmin, and other improvements. Please take a moment to review the Release Notes. Please read them carefully and report any issues via GitHub. Gen4 Planner # The newest version of the query planner, Gen4, becomes an experimental feature as part of this release.

Recently we published the first part of research comparing Graviton (ARM) with AMD and Intel CPU on AWS. In the first part, we selected general-purpose EC2 instances with the same configurations (amount of vCPU).  The main goal was to see the trend and make a general comparison of CPU types on the AWS platform only for MySQL. We didn’t set the goal to compare the performance of different CPU types. Our expertise is in MySQL performance tuning. We share research “as is” with all scripts, and anyone interested could rerun and reproduce it.
All scripts, raw logs and additional plots are available on GitHub: (2021_10_arm_cpu_comparison_c5,  …

    Multi column indexes are a powerful way to speed up queries but they are often misunderstood.  In most other databases an index on columns a, b, and c can only be used when searching on columns (a,b,& c), (a & b), and (a)  -- according to the manual. Also that index supposedly can not be used to search for (b & c) or just (c).  Well, that is the way I learned it and the way I have been teaching it. But I was wrong!  Now would be a good time to read the MySQL manual on Multiple-Column Indexes as it does not work as noted (or see the excerpt below) and I assumed MySQL worked the same way as the other databases. Well, it doesn't!

Doubt me?  Well, lets create table and add in some data. 

Table and Data

SQL > create table abcd (a serial auto_increment primary …

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

• https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
• …

RDS(Relational Database Service) comes under “Database” services of AWS(Amazon Web Services) Cloud. In this article, we will see how to create an RDS MySql Instance, provision and terminate it.

MySQL InnoDB Cluster is the official High Availability solution for and from MySQL.

MySQL InnoDB Cluster is composed by MySQL Server, MySQL Group Replication, MySQL Router and MySQL Shell.

Before InnoDB Cluster there was no standard solution for MySQL Availability and many custom solutions were used (some better than the others). But there was a good solution using some similar principles of MySQL Group Replication: Galera.

Now that MySQL InnoDB Cluster is mature and easier to orchestrate than galera, I receive a lot of requests on how to migrate from Galera (or Percona XtraDB Cluster) to MySQL InnoDB Cluster.

I already wrote some time ago an article on this process: how to migrate from Galera to MySQL Group Replication.

In this article we will see how we can migrate from Percona XtraDB …

I use CodeIgniter 4 a great deal for learning, personal projects, and application development. In this post, I cover 2 different methods you can use and retrieve the MySQL Last Insert ID value after executing an INSERT statement in CodeIgniter. Continue reading for more information…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Model Setup

Starting out, I use the CLI Generator feature and create a Model by executing this code in the terminal in the project root:

php spark make:model user --suffix

I have removed much of the …