You are here

Home

tuning

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
tags: 
query
tuning
Optimizer
sql
performance

Creating synthetic data sets for tuning SQL queries

Fri, 2020-10-02 16:50 — Shinguz
Taxonomy upgrade extras: 
performance
tuning
query
Optimizer
sql
explain
optimizing
query tuning
Performance Tuning
data

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.

Binlog format MIXED with filtering

Wed, 2015-07-15 06:11 — Shinguz
Taxonomy upgrade extras: 
fpmmm Triggers and Rules
binlog_format
filter
filtering
binary log
replication
configuration
tuning
optimizing
variables

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

Flush time is set

Wed, 2015-07-15 06:10 — Shinguz
Taxonomy upgrade extras: 
fpmmm Triggers and Rules
fpmmm
myisam
tuning
optimizing
configuration
variables

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

For further information consult MariaDB or MySQL documentation.

Table definition cache too small

Wed, 2015-07-15 06:08 — Shinguz
Taxonomy upgrade extras: 
fpmmm
table_definition_cache
optimize
tuning
configuration
parameter
fpmmm Triggers and Rules
multi-tenant

The number of table definitions (SHOW CREATE TABLE\G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables (> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency.

Table open cache too small

Wed, 2015-07-15 06:04 — Shinguz
Taxonomy upgrade extras: 
fpmmm
table_open_cache
open_files_limit
table_cache
tuning
optimizing
configuration
parameter
variables
fpmmm Triggers and Rules
multi-tenant

The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables).
Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires.

MySQL single query performance - the truth!

Fri, 2013-12-13 17:33 — Shinguz
Taxonomy upgrade extras: 
mysql
performance
Performance Tuning
query
query tuning
tuning
sidegrade

MySQL single query performance - the truth!

As suggested by morgo I did a little test for the same query and the same data-set mentioned in Impact of column types on MySQL JOIN performance but looking into an other dimension: the time (aka MySQL versions).

The answer

To make it short. As a good consultant the answer must be: "It depends!" :-)

Impact of column types on MySQL JOIN performance

Wed, 2013-12-11 20:12 — Shinguz
Taxonomy upgrade extras: 
sql
query
tuning
mysql

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns.

Deadlocks, indexing and Primary Key's

Thu, 2012-08-16 10:51 — Shinguz
Taxonomy upgrade extras: 
index
primary key
innodb
tuning
performance
Optimizer
deadlock

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

Canias optimizations

Canias is a great ERP product but there is still some potential to improve it. We only focus on MyISAM/InnoDB installations of Canias v6.0.2 in this article. For other DB back-ends those recommendations might be obsolete.

A general problem is, that in InnoDB Primary Keys are crucial in MySQL. Canias often lacks a Primary Key. In this case InnoDB will use the Unique Key for the Primary Key.

In InnoDB data are sorted by the Primary Key so it is a good idea to have the Primary Key on CLIENT and COMPANY as well to get proper sorting of the rows.

Undefined
tags: 
tuning
primary key
index
erp
canias

Impact of indices on MySQL

Fri, 2010-12-31 17:05 — Shinguz
Taxonomy upgrade extras: 
english
performance
tuning
index

It is generally well known that indexes help a lot to speed up database queries (especially SELECT but also UPDATE and DELETE). It is less known, that indexes also have some disadvantages.

One of these disadvantages is, that indexes require space in memory and on disk. An other disadvantage of indexes is, that they slow down DML statements like INSERT and DELETE.

We often see at our customers that they do not realized this behavior. Now I found the time to show the impact of (too) many indexes graphically.

DBA wisdoms

Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook

Oh no, it's not. It's much harder than that!
Bruce Pihlamae, long-term Oracle DBA



Do not assume!
Unknown IT specialist



Backups ist was für Warmduscher!
(engl. Backup is for sissies!)
Unkown DBA



Yesterday (the DBA version)

English
tags: 
Backup
DBA
tuning
database administrator

Performance Tuning Key for MySQL

This MySQL Performance Tuning Key should give you a guide how to best tune you MySQL database systematically... It should also work similar for other RDBMS.

Also check our MySQL Performance Monitor

For a database configuration tuning only please look first at our MySQL database health check.

Undefined
tags: 
mysql
performance
tuning

MySQL Database Health Check

Sat, 2009-11-07 22:13 — oli

Release v0.28

  • On this page you can check the health of your MySQL/MariaDB database.
  • If you want to find out how the MySQL Health Check works try out the examples.
  • If you are logged in, our CMS remembers your entry for your next visit.
  • If we should send you some advices about the findings please fill in the following form:
Contact
Name: Undefined
tags: 
mysql
performance
database
tuning
health check
Subscribe to RSS - tuning