Search Results for: what fields should I index

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 […]

Recover BLOB fields

For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn’t defined the primary index. The PRIMARY index pages are […]

InnoDB TABLE/INDEX stats

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | rows | estimated number of all rows […]

Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there […]

How to deal with MySQL deadlocks

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the […]

Row-based replication, MySQL 5.6 upgrades and temporal data types

Whither your rollback plan? MySQL 5.6 upgrades are in full swing these days and knowing how to safely upgrade from MySQL 5.5 to 5.6 is important. When upgrading a replication environment, it’s important that you can build a migration plan that safely allows for your upgrade with minimal risk — rollback is often a very […]

Q&A: Common (but deadly) MySQL Development Mistakes

On Wednesday I gave a presentation on “How to Avoid Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides. Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time […]

10 MySQL settings to tune after installation

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list […]