You are here

tuning

MySQL single query performance - the truth!

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

Taxonomy upgrade extras: 

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

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

Undefined

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.

Impact of indices on MySQL

Taxonomy upgrade extras: 

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

English

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)

Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Performance Tuning Key for MySQL

Undefined

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.

MySQL Database Health Check

Undefined

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:
tags: 
Subscribe to RSS - tuning