As a developer using Python, I want to be able to hand a list to an SQL statement with a WHERE id IN (…) clause, and it should do the right thing.

Well, that is not how it started, because it was asked on the internal no-work-channel, so it kind of escalated more.

A question

The original question was:

Dev> Why is it 2021, and SQL prepared statements still can’t deal with IN? Or have I missed some exciting development?

After a quick detour through Java (which we won’t discuss any further in this article), we established that this was a Python problem in this particular instance. And we touched on several other interesting things on our way.

But first, the solution:

#! /usr/bin/env python3

import click
import MySQLdb
import MySQLdb.cursors

class …

As a developer using Python, I want to be able to hand a list to an SQL statement with a WHERE id IN (…) clause, and it should do the right thing.

Well, that is not how it started, because it was asked on the internal no-work-channel, so it kind of escalated more.

A question

The original question was:

Dev> Why is it 2021, and SQL prepared statements still can’t deal with IN? Or have I missed some exciting development?

After a quick detour through Java (which we won’t discuss any further in this article), we established that this was a Python problem in this particular instance. And we touched on several other interesting things on our way.

But first, the solution:

#! /usr/bin/env python3

import click
import MySQLdb
import MySQLdb.cursors

class DebugCursor(MySQLdb.cursors.DictCursor):
    def _query(self, q):
        print(f"Debug: {q}") …

     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.