Emergency

Search Results for: explain possible keys

Getting EXPLAIN information from already running queries in MySQL 5.7

When a new version of MySQL is about to be released we read a lot of blog posts about the performance and scalability improvements. That’s good but sometimes we miss some small features that can …

Read More
 

Percona Toolkit collection: pt-visual-explain

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools. Have you ever had a problem understanding the EXPLAIN statement output? And are you …

Read More
 

When EXPLAIN estimates can go wrong!

I have been working with a few customer cases and one interesting case popped up. The customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally …

Read More
 

EXPLAIN EXTENDED can tell you all kinds of interesting things

While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your …

Read More
 

Possible optimization for sort_merge and UNION ORDER BY LIMIT

Every so often you need to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion of the index for the order …

Read More
 

Using CHAR keys for joins, how much is the overhead ?

I prefer to use Integers for joins whenever possible and today I worked with client which used character keys, in my opinion without a big need. I told them this is suboptimal but was challenged …

Read More
 

When EXPLAIN can be misleading

One think I can see with people using EXPLAIN is trusting it too much, ie assuming if number of rows is reported by EXPLAIN is large query must be inefficient. It may not be the …

Read More
 

Extended EXPLAIN

One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information …

Read More
 

MySQL EXPLAIN limits and errors.

Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of …

Read More
 

Advanced Query Tuning in MySQL 5.6 and MySQL 5.7 Webinar: Q&A

Thank you for attending my July 22 webinar titled “Advanced Query Tuning in MySQL 5.6 and 5.7” (my slides and a replay available here). As promised here is the list of questions and my answers …

Read More