Search Results for: select fields

Increasing slow query performance with the parallel query execution

MySQL and Scaling-up (using more powerful hardware) was always a hot topic. Originally MySQL did not scale well with multiple CPUs; there were times when InnoDB performed poorer with more  CPU cores than with less CPU cores. MySQL 5.6 can scale significantly better; however there is still 1 big limitation: 1 SQL query will eventually use only […]

Using the new spatial functions in MySQL 5.6 for geo-enabled applications

Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are: Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile […]

How to recover table structure from InnoDB dictionary

To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from […]

Adventures in archiving

One of our Remote DBA service clients recently had an issue with size on disk for a particular table; in short this table was some 25 million rows of application audit data with an on disk size of 345GB recorded solely for the purposes of debugging which may or may not occur. Faced with the task of […]

CVE-2012-4414 strikes back in MySQL 5.5.29 (and what we’re doing in Percona Server 5.5.29)

In preparing Percona Server 5.5.29 (not yet released, but soon), I filed MySQL bug 68045 (is marked private as it’s a security bug). This bug is in relation to the Oracle fix for CVE-2012-4414 and a problem I found with it. The MariaDB fix (which we incorporated into Percona Server 5.5.28-29.3) is not affected. When the […]

Full table scan vs full index scan performance

Earlier this week, Cédric blogged about how easy we can get confused between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according to the documentation the 2nd […]

Timezone and pt-table-checksum

I recently worked through an issue with a client trying to detect data drift across some servers that were located in different timezones.  Unfortunately, several of the tables had timestamp fields and were set to a default value of CURRENT_TIMESTAMP.  From the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are […]

On Character Sets and Disappearing Tables

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we […]