Any introduction to Prepared Statements has to start with SQL Statements.

Every SQL Statement arriving at a database is processed before executing and returning a resultset. The processing is a series of computations including: parsing, compiling, and query optimization.

The parsing, compiling, and query optimization can lead to a lot of wasted CPU if your queries are very similar, like this example from the widely available test database Sakila:

Example Queries:

  SELECT * FROM address WHERE address_id = 12;

  SELECT * FROM address WHERE address_id = 34;

  SELECT * FROM address WHERE address_id = 56;

Example Code, assuming PDO & PHP, to produce those queries:

  $ids = array(12,34,56);

  $sql = “SELECT * FROM address WHERE address_id = “;

…

Shard-Query inserts data into a “coordinator” table when answering queries.   When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes.   Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.

For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.

Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the coordinator table, which serializes queries at the bulk insert stage.  Having to insert the …

It's now two.. three.. four weeks since Percona MySQL Conference was finished.. - and there are still many things and new ideas running in my mind inspired by this great MySQL tech + community event (regardless many public holidays and spring vacations in May (which is also the reason why I'm late with this article ;-)). There was a lot of things to learn from others, tons of stuff to share, and as well enjoy long and deep discussions with many MySQL users/ hackers/ gurus/ mentors/ devs/ etc.. etc.. We were very proud to speak about MySQL 5.6 GA during this conference, and also present the first labs release of the future MySQL 5.7 version (and you may already download and play with it, or compile yourself from sources which are always here).. - don't miss presentation made by Tomas about all MySQL Innovation happening today @Oracle! And if MySQL 5.6 for today is the best MySQL …

Continuent Tungsten offers real-time replication from MySQL to a variety of DBMS types including Vertica. In this Tungsten University webcast we will show you the details of setting up MySQL-to-Vertica replication, including the following topics:

Introduction to Continuent Tungsten features for data warehouse loading Installation for MySQL to Vertica replication Best practices for

The May Boston MySQL User Group featured John Hugg of VoltDB talking about determinism and databases. I have uploaded the hour-long video to http://www.youtube.com/watch?v=mTDLyRauJtw. Seasoned MySQLers will nod their heads because the problems described are familiar, and those who are not exactly sure what “determinism and databases” means will learn a lot.

Enjoy!

(As always, videos are free on YouTube with no login or attempt to solicit your e-mail address or any other information)

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation where he mentioned that “one of our worst performing views on SUMO is doing waaaayyy better with the upgraded databases”, that it “seems more stable” and that “I stopped receiving ‘MySQL went away or disconnected emails’ which came in once in a while.”

It’s always nice to see upgrades actually making a difference. In our case we saw a lot less CPU wait, though that might also be partially due to tuning the memory settings on the machines and adding in another read slave to handle queries. As a result, network traffic throughput went from less than 1 Mb/sec to about 18 Mb/sec, because the machines were just handling more queries per second, period.

(I had this e-mail as a draft …

I got a question a while ago that I thought was quite simple, but turned into an interesting discussion: How much hard disk space does a database take up?

As it happens, there's a simple answer and a much, much more involved (yet ill-defined) answer, and which one you choose depends on what you think goes into a database and how very, very pedantic you are.

The Simple Answer

You can query the size of table and index data using Information Schema or SHOW TABLE STATUS. The columns DATA_LENGTH and INDEX_LENGTH contain the number of bytes for the table and index data respectively. You can construct simple queries based on that data, or do a quick search on the web for more interesting queries (For example, Peter Zaitsev of Percona wrote a post …

“Application designers need to start by thinking about what level of data integrity they need, rather than what they want, and then design their technology stack around that reality. Everyone would like a database that guarantees perfect availability, perfect consistency, instantaneous response times, and infinite throughput, but it´s not possible to create a product with [...]

The main benefit of using GTIDs is to have much easier failover than with file-based replication. We will see how to change the replication topology when using GTID-based replication. That will show where GTIDs shine and where improvements are expected.

This is the second post of a series of articles focused on MySQL 5.6 GTIDs. You can find part one here.

Our goal will be to go from setup #1 to setup #2 on the picture below, following various scenarios:

For these tests, all servers are running on 127.0.0.1 with ports ranging from 10000 for s0 to 10004 for s4.

Scenario #1: All slaves have processed all the writes

This is the easiest case, we will …

MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

CREATE TABLE `history` (
   `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `u_id` int(10) unsigned NOT NULL,
   `cn_id` int(10) unsigned NOT NULL,
   `f_id` int(10) unsigned NOT NULL
   PRIMARY KEY (`h_id`)
 ) ENGINE=InnoDB

If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: history
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 19908716 …