You are here

Optimizer

Comparing Optimizer Results

A while ago I read a paper about Optimizer Benchmarks: How Good Are Query Optimizers, Really? by Viktor Leis from Fakultät für Informatik of the Technische Universität München.

SQL Query Tuning - Performance

How could the following SQL queries be improved performance wise and otherwise and can you also explain why your change is more optimal?

Please consider, when testing, that your results are not confused by the Query Cache or by reading data from your I/O system which are an order of magnitude faster (Query Cache) or slower (I/O) than the in-memory behaviour.

Undefined

Creating synthetic data sets for tuning SQL queries

When it comes to SQL Query tuning with customers we often get the slow running SQL query and possibly, in good cases, also the table structure. But very often, for various reasons, we do not get the data.

MariaDB indexing of NULL values

Taxonomy upgrade extras: 

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values... And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

Find evil developer habits with log_queries_not_using_indexes

Recently I switched on the MariaDB slow query logging flag log_queries_not_using_indexes just for curiosity on one of our customers systems:

Deadlocks, indexing and Primary Key's

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

Wie der MySQL Optimizer schummelt, wenn es um MySQL Cluster geht...

Bei einem Kunden sind wir auf ein nettes Beispiel gestossen, wie der MySQL Optimizer schummelt, wenn er mit dem MySQL Cluster zusammen arbeiten sollte. Dieser Kunde hat Abfragen, welche auf dem Entwicklungssystem nicht sonderlich langsam gelaufen sind. Aber wenn er diese Abfragen auf dem Abnahmetest-System (mit wesentlich mehr Daten) ausführt, benötigen diese viel zu viel Zeit, was für ihn nicht akzeptabel ist, da diese Abfragen mehrmals pro Sekunde abgesetzt werden können.

How the MySQL Optimizer with MySQL Cluster is cheating you...

Taxonomy upgrade extras: 

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

Subscribe to RSS - Optimizer