You are here

sql

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 SQL Error Log Plugin

Taxonomy upgrade extras: 

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something "new" I did not know yet...

FromDual Schulung MySQL und SQL für Einsteiger

FromDual bietet zusammen mit der GFU Cyrus GmbH in Köln vom 17. - 21. Oktober 2016 eine MySQL und SQL Schulung für Einsteiger an.

Anmelden können Sie sich unter Schulungstermine für MySQL und MariaDB.

Why you should take care of MySQL data types

Taxonomy upgrade extras: 

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

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.

Example 1


CREATE TABLE `order` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(64) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `order` VALUES
  (NULL, 'Test order 1')
, (NULL, 'Test order 2')
, (NULL, 'Test order 3');


CREATE TABLE pos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, order_id INT UNSIGNED NOT NULL
, name VARCHAR(64) NOT NULL
, amount SMALLINT NOT NULL
, price DECIMAL (6,2) NOT NULL
, status TINYINT NULL
) ENGINE = InnoDB;

INSERT INTO pos VALUES
  (null, 1, 'Schrauben', 50, 0.10, 0)
, (null, 1, 'Muttern', 50, 0.10, 0)
Undefined
Subscribe to RSS - sql