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 …

The Open DB Camp in Sardinia 2011 has had a number of sessions on varying topics. Topics range from MySQL over MongoDB to replication and High Availability.

I decided to tap into the database expert resources present here at Sardegna Ricerche by discussing a non-database issue, where one can expert database experts to have insights beyond those of end users. And they did.

The topic was the particular case of information overload many of us suffer from on our hard disks: Too many files, too hard to find.

• How do we find the bank statement from April 2007 from the more-seldom-used account?
• What are the ten best work-related pictures from last year?
• Is this the most current …

Introduction

If you, like me, are building or thinking of implementing a MySQL-powered application that has any need for prioritizing selecting certain data over other data, this article is for you.

Example

As a real world example, consider a queue-like video processing system. Your application receives new videos and processes them. The volume of incoming videos can at times be higher than the processing rate because the process is CPU bound, so occasionally a pretty long queue may form. You will try to process them as fast as you can but…

Note that I am using a queue here, so the the next item to be processed is a result of sorting by some sort of field in a ascending order, for example ORDER BY id or ORDER BY upload_date. Iâ€ll pick the id sort here.

…suddenly, you need to process a video somewhere in the middle of the queue or an important video enters and …