One optimization that I’ve employed several times involves batching write queries. MySQL has some very efficient ways to load multiple rows of data in a single query. Multi-row inserts are one common way to do it, but if you’re adventurous you can also try using LOAD DATA INFILE. Multi-row inserts are just what you would [...]

Catching up on our reading today, we saw a blog post from Chris Stevens, the multi-faceted VP of Engineering at Traxo, technology consultant, and Full Stack developer. In his post, he notes some of the impressive new database technologies he’s seeing lately in tech blogs and on the conference circuit.

Chris says that when it makes sense he advises his clients to look at non-relational datatstores, but for a number of reasons he works with MySQL wherever possible. Many of his clients looking to run globally distributed applications often need to distribute the database across more than one data center (geo-distribution) for fault tolerance and localized performance.

“Globally distributed applications often require localized performance,” he said in his post. “We can do some work loading front-end assets from CDN …

Thank you for this information. It's hard to find advanced, detailed information about InnoDB outside of just a few sources. I second the request for you to write a book.

Poor pt-heartbeat 2.1: it was perfectly fine through Percona Toolkit 2.1.7, but it’s had a rough life since. Its troubles are finally over as of Percona Toolkit 2.1.10. At a conference recently, a user joked to me that “Percona Toolkit is awesome, but you really broke pt-heartbeat.” It’s true, and if you haven’t heard or experienced the story, here it is for the record.

Since the dawn of time, pt-heartbeat had computed slave lag like:

t1=time (Perl) --> replicate --> t2=time (Perl); lag = t2 - t1

Surprisingly, that worked for many years despite what should have been an obvious problem: different time zones: t1 could be in PST …

In 4 days, the MySQL Performance Tuning training teaches you practical, safe and highly efficient ways to optimize performance for the MySQL Server. It will help you:

• Evaluate the architecture
• Understand and use the tools.
• Configure the database for performance.
• Tune application and SQL code.
• Tune the server.
• Examine the storage engines.
• Assess the application architecture.
• Understand general tuning concepts.

You can take this instructor-led course as a:

• Training-on-Demand offering: Start training within 24 hours of regsitration, taking this course at your own pace through streaming video of instructor delivery and …

Dr Charles Bell and I recently presented a webinar on MySQL Utilities; there was a heavy focus on what you can acheive with them and how you should use them. In case you couldn’t attend or want to listen to some of the details again, the replay from that webinar is available here.

Abstract:

MySQL Utilities provide a collection of command-line utilities that are used for maintaining and administering MySQL databases, including:

• Admin Utilities (Clone, Copy, Compare, Diff, Export, Import)
• Replication Utilities (Setup, Configuration, Automated Slave Promotion)
• General Utilities (Disk Usage, Redundant Indexes, Search Meta Data)

Andrew Morgan and Chuck Bell will …

We are happy to announce a new release of XAMPP for all platforms! This is a minor release that fixes the following issues in both XAMPP versions:

1.8.2-1 version (PHP 5.4 based)

• Fix issue with RedHat-based Linux distros (i.e CentOS) in which the start script would fail to autodetect the distribution and start the servers.
• Create "mysql" user if it does not already exist on Linux. Now MySQL runs as "mysql" user by default.
• Change Apache and ProFTP users to "daemon" on Linux. The previous "nobody" user does not exist on some Linux distributions by default (i.e. CentOS).
• Fix Mercury Mail configuration on Windows.
• Fix XDebug issue on Windows. The previous module did not work with this PHP version.

1.8.3-0 version (PHP 5.5 based)

• Updated PHP to 5.5.1 version for Windows, OS X and Linux.
• Fix issue with RedHat-based Linux distros (i.e …

This Log Buffer Edition marvels at the ease, simplicity, and power of the database bloggers as they go about their adventures in the realms of Oracle, SQL Server, and MySQL.

Oracle:

The new Oracle Database 12c’s “Implicit Result Sets” (IRS) feature allows query results to be returned from a stored PL/SQL procedure (or a PL/SQL anonymous block) without requiring a special PHP code.

Ranka has been spending a lot of time on big data and its application, architecture, and processes.

Kashif Manzoor tells us how to use the new Clone Log Parser utility in Oracle E-Business Suite.

…

Recently a forum question [ 1 ] got my attention:

Is there any performance issue with Union?

I used union all sometime back and it was performance issue just to make an opinion that we should used union in query.

The question itself was not too interesting because the answer is easy: It depends. But I wanted to see if there was an improvement in this common problem over time in MySQL.

Test set-up

So I prepared a little test to simulate some of the possible scenarios:

CREATE TABLE `u` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` …

Hi Joe,

Thanks for pointing that out.

I’ve downloaded that tool and tested, but I confess that I found it heavier than other SQL tools I used previously.

Regardless, their free version also offers SSH tunnel to connect to your MySQL which is brilliant!

Thanks once again.