Originally posted at Andres’s blog. Traditional query optimizing is mostly about two things: first, in which order and from where to access data, and then how to then combine it. You have probably seen the tree shapes execution plans that are produced from query planning. I’ll use an example from the MySQL docs, using FORMAT=TREE which was introduced in MySQL 8.0: mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.
10 Older Entries »
Computers are dumb. And they will do exactly what you ask them to do. The trick often is to think as dumb as the computer. Sadly it is all to easy to assume that the computer is 'thinking' like you are and blunder into a head scratching puzzle. Recently there was a post on MySQL Community Space Groundbreakers Developer Community site that shows that sometimes what is intended is not what you want but you are getting exactly what you asked.
Quiz -- What happens if you run the following query?
SELECT concat('CREATE TABLE if does not exists
' like sakila.',
WHERE TABLE_SCHEMA = 'sakila'
A) You will …[Read more]
Both MongoDB and the MySQL Document Store are JSON document
stores. The syntax differences in the two products are very
interesting. This long will be a comparison of how commands
differ between these two products and may evolve into a 'cheat
sheet' if there is demand.
I found an excellent Mongo tutorial Getting Started With MongoDB that I use as a framework to explore these two JSON document stores.
The DataI am using the primer-dataset.json file that MongoDB has been using for years in their documentation, classes, and examples. MySQL has created the world_x data set based on the world database used for years in documentation, classes and examples. The data set is a collection of JSON documents filled with restaurants around Manhattan.
More and more people are using ProxySQL because it is a great tool and it can help DBAs a lot. But many people do not realize that it is more powerful than it looks. It has many features and possibilities. I am going to show you one of my favorite “tricks” / use cases.
There are plenty of blog posts explaining how ProxySQL works. I am not going to that again. Instead, let’s jump straight to the point. There is a table in ProxySQL called “stats.stats_mysql_query_digest”. It is one of my favorite tables because it basically records all the queries that were running against ProxySQL. Without collecting any queries on the MySQL server, I can find …[Read more]
Occasionally at conference or a Meetup, someone will approach me
and ask me for help with a MySQL problem. Eight out of ten
times their difficulty includes a sub query. "I get an error
message about a corrugated or conflabugated sub query or some
such," they say, desperate for help. Usually with a
bit of fumbling we can get their problem solved. The
problem is not a lack of knowledge for either of us but that sub
queries are often hard to write.
MySQL 8 will be the first version of the most popular database on the web with Common Table Expressions or CTEs. CTEs are a way to create temporary tables and then use that temporary table for queries. Think of them as easy to write sub queries!
WITH is The Magic WordThe new CTE magic is indicated with the WITH clause.
mysql> WITH myfirstCTE[Read more]
AS (SELECT * FROM world.city WHERE …
Welcome to another post in the series of Percona Live featured tutorial speakers blogs! In these blogs, we’ll highlight some of the tutorial speakers that will be at this year’s Percona Live conference. We’ll also discuss how these tutorials can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!
In this Percona Live featured tutorial, we’ll meet Øystein Grøvlen, Senior Principal Software Engineer at Oracle. His tutorial is on How to Analyze and Tune MySQL Queries for Better Performance. SQL query …[Read more]
Last week, 21-23 September, it took place the European MySQL Conference, or “Data performance Conference” as this year’s subtitle was “MySQL. NoSQL. Data in the cloud.”. This year, it changed its location from London to Amsterdam and, as most people I talked to agreed, the change was for good. As every year, Percona was the company organizing it, but it had the participation of all the major players in the open source MySQL/MongoDB/Cloud data world. Special mention goes to Booking.com, which had more …[Read more]
Why Should I Be Reading This?
To better understand how the MySQL Server functions, how to monitor the relevant server events, and find out what’s new in MySQL 5.7.8.
What’s Special About the Audit Plugin API?
Picking the right API for your new plugin is probably the most important design decision a plugin author will need to make.…
This Log Buffer edition transcends beyond ordinary and loop in few of the very good blog posts from Oracle, SQL Server and MySQL.
- Variable selection also known as feature or attribute selection is an important technique for data mining and predictive analytics.
- The Oracle Utilities SDK V220.127.116.11.2 has been released and is available from My Oracle Support for download.
- This article provides a high level list of the new features that exist in HFM 18.104.22.168 and details the changes/differences between HFM 22.214.171.124 and previous releases.
- In recent …
Long running transactions can be problematic for OLTP workloads, particularly where we would expect most to be completed in less than a second. In some cases a transaction staying open just a few seconds can cause behaviour that is entirely unexpected, with the developers at a loss as to why a transaction remained open. There are a number of ways to find long running transactions, luckily versions of MySQL from 5.6 onwards provide some very insightful instrumentation.
Here we will use the information_schema coupled with the …[Read more]
10 Older Entries »