Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…[Read more]
10 Older Entries »
Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.
It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …[Read more]
Kubernetes operators are meant to simplify the deployment and management of applications. Our Percona Kubernetes Operator for Percona XtraDB Cluster serves the purpose, but also provides users the flexibility to fine-tune their MySQL and proxy services configuration.
The document Changing MySQL Options describes how to provide custom
configuration to the operator. But what would happen if you made a mistake and specified the wrong parameter in the configuration?
I already deployed my Percona XtraDB Cluster and deliberately submitted the wrong
spec: ... pxc: configuration: …[Read more]
At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?”
As a developer, I just want to automatically identify and isolate slow queries!
Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context.
There is also work done by a few developers which instead collects query strings, query execution times and query counts at the application. This has access to the call stack, so it can tell you which code generated the query that was slow.
It …[Read more]
All together now
Let’s now put together all that we’ve learned in InnoDB Data Locking – Part 2 “Locks” about table and record locks to understand following situation:
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t FOR SHARE; +----+ | id | +----+ | 5 | | 10 | | 42 | +----+ 3 rows in set (0.00 sec) mysql> DELETE FROM t WHERE id=10; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t VALUES (4); Query OK, 1 row affected (0.00 sec) mysql> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE FROM performance_schema.data_locks
… Facebook Twitter LinkedIn
I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.
In fact, he adds a
prequel_film_id column to the
film table in the
sakila database and
then a single row to demonstrate a minimal self-join query. I
wanted to show them how to view a series of rows interconnected
by a self-join, like the following:
SELECT f.title AS film , fp.title AS prequel FROM film f LEFT JOIN film fp ON f.prequel_id = fp.film_id WHERE f.series_name = 'Harry Potter' AND fp.series_name = 'Harry Potter' ORDER BY f.series_number;
It returns the following result set:
MySQL highly available solutions, InnoDB Cluster (it uses InnoDB storage engine and is based on Group Replication plugin) and NDB Cluster (NDB storage engine), offer high scalability and redundant topologies.
- InnoDB Cluster can be configured with up to 9 replicas, in single primary configuration or multi-primary.
- NDB Cluster instead, while being a much different solution, offers the chance to have …
Logical backup’s are of great use in data migration across cloud environments and table level recoveries. The new Mysql shell 8.0.22 ,has introduced a couple of new utilities named util.dumpTable() and util.exportTable() to export individual tables from a MySQL. Prior to 8.0.22 it is not possible to make a backup of single table using MySQL Shell.
MySQL Shell’s new table dump utility util.dumpTables() from this we can take the dump of the specific tables of the schema using this utility.it works in the same way as the instance dump utility util.dumpInstance() …
Recently the MySQL Database Service (MDS) was launched in Oracle Cloud Infrastructure (OCI). As Cloud is about automation you don't have to use the Web Console to configure your instances, but can do it via API, for instance with the oci command line tool or your favorite programming language. However often it is nice to define the world in a declarative way ("I want a network, like this, and a MySQL database like that") and let the tool figure out how to align the reality in the cloud with your wish. A tool doing this is Terraform.
With Terraform you can declare the state in description files, the tool creates a dependency graph and then applies what has to be applied and of course it supports OCI and as part of the default OCI Provider, there is even directly …[Read more]
The Challenge Often with sensitive information, you need to have an audit log. Frequently data such as this will contain a classification level as part of the row, defining policies for how it is handled, audited, etc. In a prior blog I discussed how to audit the selection of classified data.… Facebook Twitter LinkedIn
10 Older Entries »