Showing entries 1 to 10 of 13
3 Older Entries »
Displaying posts with tag: select (reset)
Non-Deterministic Order for SELECT with LIMIT

In this blog, we’ll look at how queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).

Short story:

Do not rely on the order of your rows if your query does not use


. Even with


, rows with the same values can be sorted differently. To fix this issue, always add


 when you have



Long story:

While playing with MariaDB ColumnStore and Yandex ClickHouse, I came across a very simple case. In MariaDB ColumnStore …

[Read more]
Exploring Metadata Locks with gdb - Double Checking the Initial Results

Some results in my initial post in this series led me to questions that I'll try to answer here. First of all, I noted that SELECT from a single table ended up with just one metadata lock request:

(gdb) b MDL_request::init
Breakpoint 1 at 0x648f13: file /usr/src/debug/percona-server-5.6.27-76.0/sql/, line 1266.
Breakpoint 2 at 0x648e70: file /usr/src/debug/percona-server-5.6.27-76.0/sql/, line 1245.
warning: Multiple breakpoints were set.
Use the "delete" command to delete unwanted breakpoints.
(gdb) c
[Switching to Thread 0x7ff224c9f700 (LWP 2017)]

Breakpoint 2, MDL_request::init (this=0x7ff1fbe425a8,
    mdl_namespace=MDL_key::TABLE, db_arg=0x7ff1fbe421c8 "test",

[Read more]
More on (transactional) MySQL metadata locks

Two years ago Ovais Tariq had explained in detail what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for users – DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.

I decided to do this as …

[Read more]
Building Queries Systematically

The SQL language is a bit like a toolkit for data. It consists of lots of little fiddly bits of syntax that, taken together, allow you to build complex edifices and return powerful results. For the uninitiated, the many tools can be quite confusing, and it's sometimes difficult to decide how to go about the process of building non-trivial queries, that is, queries that are more than a simple SELECT a, b FROM c;

A System for Building Queries

When you're building queries, you could use a system like the following: 

  1. Decide which fields contain the values you want to use in our output, and how you wish to alias those fields
    1. Values you want to see in your output
    2. Values you want to use in calculations . For example, to calculate margin on a product, you could calculate price - cost and give it the alias margin.
    3. Values you want …
[Read more]
When kill flag is checked for SELECT? Part II

In the previous part I've stopped at the moment when we entered JOIN:exec() - most checks for kill flag happen somewhere there, during query execution. We know the list of functions that checks this flag during query execution:


but we do not know when exactly each of them is called. So, let me try to show what happens inside JOIN::exec (some code paths and checks are not considered for simplicity, we care about SELECT, but not EXPLAIN SELECT etc). I've included statements that change thread status and highlighted parts of code …

[Read more]
When kill flag is checked for SELECT? Part I

Manual describes this briefly:

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
Complete, correct and useful answer is more complex though. Here is correct answer, but not very useful. So, kill flag is checked in the following functions related to SELECT statement processing:


[Read more]
When EXPLAIN estimates can go wrong!

This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...

The post When EXPLAIN estimates can go wrong! appeared first on ovais.tariq.

SQL Trouble with dummy tables

As I’m mostly using Oracle for work projects, the concept of the DUAL dummy table has become quite intuitive. I hardly ever think about the days when I was playing around with that table to find out its purpose (e.g. writing into it when DUAL was still a physical object, and thus killing the whole database…)

In many other RDBMS, there is no need for dummy tables, as you can issue statements like these:

SELECT 1 + 1;

These are the RDBMS, where the above is generally possible:

  • H2
  • MySQL
  • Ingres
  • Postgres
  • SQLite
  • SQL Server
  • Sybase ASE

In other RDBMS, dummy tables are required, like in Oracle. Hence, you’ll need to write things like these:

[Read more]
Limited SELECT count(*)

A SELECT statement with COUNT returns the number of rows retrieved by the SELECT statement (see mysql select count). For performance reasons, the desired result is to limit that count. Including a LIMIT clause in the SELECT statement will not work since it only restricts the number of rows returned, which is always one. The […]


While learning a new ORDER BY syntax recently, as a diligent architect/DBA I reviewed the documentation. What I also found in the SELECT syntax which I did not also know was the keyword DUMPFILE.

The SELECT Syntax from MySQL 5.1 Manual states:

If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

It’s a shame there is no middle ground, where you get the features of OUTFILE (i.e. all rows), and the features of DUMPFILE (i.e. no heading)

Showing entries 1 to 10 of 13
3 Older Entries »