For a long time, the only algorithm for executing a join in MySQL has been variations of the nested loop algorithm. With the release of MySQL 8.0.18, the server can now execute joins using hash join. This blog post will have a look at how it works, when it is used, and how it compares to the old join algorithms in MySQL in terms of performance.…
Facebook Twitter LinkedIn
MySQL 8.0.18 was just released, and it contains a brand new feature to analyze and understand how queries are executed: EXPLAIN ANALYZE.
What is it?
EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why.…
Facebook Twitter LinkedIn
I have to celebrate the anniversary of my last day in Oracle
(that was 7 years ago!) somehow, and I think writing yet another
blog post about Oracle MySQL bugs is a good way to do this. I am
actually surprised (and happy) that public bugs database is still
alive, maintained and considered important in Oracle, and I know
who in Oracle was working hard all these years for this to
happen!
In my previous post in this series I've stopped
on Bug #95954 and had not completed review of
interesting MySQL bug reports that I've subscribed to in June
2019. So, below I start with the next bug in my list, complete
review for June and cover some bugs reported in July. There were
many.
Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes ! This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015. But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.
The MySQL Optimizer
Before looking at the problematic query, we have to say a few words about the optimizer. The Query Optimizer is the part of query execution that chooses the query plan. A Query Execution Plan is the way MySQL chooses to execute a specific query. It …
[Read more]
This week new minor releases of MySQL server were announced. Of
them all I am mostly interested in MySQL 5.7.27. I plan to concentrate on InnoDB,
replication and optimizer bugs reported in public MySQL bugs
database and fixed in MySQL 5.7.27. As usual I name bug reporters
explicitly and give links to their remaining currently active bug
reports, if any.
Let me start with InnoDB bug fixes:
(Image credit: Pixabay).
In my earlier post, I showed how MySQL, since version 8.0.14, has support for LATERAL derived tables. With LATERAL, a JOIN can have a second table – a subquery-based derived table – be defined based on values from columns of the first table, and thus be re-calculated for each row of the first table.…
Facebook Twitter LinkedIn
My previous post from this series was published
more than 1.5 years ago. I had never planned to stop writing
about my everyday work on a regular basis, but sometimes it's not
easy to pick up something really interesting for wider MySQL
audience and when in doubts I always prefer to write about MySQL
bugs...
In any case, any long way starts from the first step, so I
decided to write one post in this series per week and try to
summarize in it whatever findings, questions, discussions, bugs
and links I've collected over the week. My work experience
differs week after week, so some of these posts may be boring or
less useful, but I still want to try to create them on a regular
basis.
I was working on (upcoming) blog post (inspired by one customer
issue) on …
We have a public holiday here today and it's raining outside for
a third day in a row already, so I hardly have anything better to
do than writing yet another review of public MySQL bug reports
that I've subscribed to recently.
Not sure if these reviews are really considered useful by anyone
but few of my readers, but I am still going to try in a hope to
end up with some useful conclusions. Last time I've stopped on Bug
#94903, so let me continue with the next bug in my
list:
Oracle released minor MySQL Server versions in all supported
branches on April 25, 2019. MySQL 5.7.26 is just one of them, but recently I prefer to ignore MySQL 8 releases
(after checking that I can build them from source code at least
somewhere, even if it takes 18G+ of disk space and that they work
in basic tests), as there are more chances for MySQL 5.7 bug
fixes to affect me (and customers I care about) directly.
So, in this yet another boring blog post (that would never be a
reason for any award) I plan to concentrate …
I have not much yet to say on a popular topic of upgrading everything to MySQL 8, so let me just continue reviewing public MySQL bug reports that I've subscribed to recently. After my previous post at least one bug, Bug #94747, got enough comments and clarifications (up to specific commit that introduced this regression pointed out by Daniel Black!) to have it re-classified and verified as InnoDB code bug. So, I see good reasons to continue attracting wide public attention …
[Read more]