Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 58 10 Older Entries

Displaying posts with tag: explain (reset)

Getting EXPLAIN information from already running queries in MySQL 5.7
+0 Vote Up -0Vote Down

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 help us a lot in our day-to-day tasks. One good example is the blog post that Aurimas wrote about a new small feature in MySQL 5.6 that I didn’t know about until I read it: the Automatic InnoDB transaction log file size change. How cool is that?

I plan to write a series of blog posts that will show some of those small …

  [Read more...]
New PERFORMANCE_SCHEMA defaults in MySQL 5.7.7
+0 Vote Up -0Vote Down

I thought it was worth a moment to reiterate on the new Performance Schema related defaults that MySQL 5.7.7 brings to the table, for various reasons.

For one, most of you might have noticed that profiling was marked as deprecated in MySQL 5.6.7. So it is expected that you invest into learning more about Performance Schema (and Mark’s sys schema!).

Second, there are lots of virtual environments and appliances out there running Community Edition MySQL where Performance Schema can be a useful tool for analyzing performance. Thus, expect to …

  [Read more...]
Optimizer hints in MySQL 5.7.7 – The missed manual
+1 Vote Up -0Vote Down

In version MySQL 5.7.7 Oracle presented a new promising feature: optimizer hints. However it did not publish any documentation about the hints. The only note which I found in the user manual about the hints is:

  • It is now possible to provide hints to the optimizer by including /*+ ... */ comments following the …
  [Read more...]
Percona Live Presentation: Improving Performance With Better Indexes
+0 Vote Up -0Vote Down

The slides for my Improving Performance With Better Indexes presentation at Percona Live 2015 MySQL Conference and Expo are now available.

In this presentation I discuss how to identify, review and analyze SQL statements in order to create better indexes for your queries. This includes understanding the EXPLAIN syntax and how to create and identify covering and partial column indexes.

  [Read more...]
EXPLAIN minor wishlist
+0 Vote Up -0Vote Down

While we always want better performance and more and larger features for MySQL, those cannot just “magically appear” from one version to another, requiring deep architecture changes and lots of lines of code. However, there are sometimes smaller features and fixes that could be implemented by an intern or an external contributor, mainly at SQL layer, and that could make the MySQL ecosystem friendlier to newbies and non-experts. Making a piece of software easier to use is sometimes overlooked, but it is incredibly important -not everybody using MySQL is a DBA, and the more people adopting it, more people will be able to live from it, both upstream and as third party …

  [Read more...]
Priority queue optimization for filesort is now visible in MariaDB 10.0
+1 Vote Up -0Vote Down

TL;DR: Priority queue optimization for filesort with small LIMIT is now visible in MariaDB: there is a status variable and you can also see it in the slow query log (KB page link).

A longer variant:
One of the new optimizations in MySQL 5.6 is ability to use a priority queue instead of sorting for ORDER BY … LIMIT queries. The optimization was ported into MariaDB 10.0 long ago, but we still get questions if/when it will be ported. I guess, the reason for this is that, besides the query speed, you …

  [Read more...]
New feature in MariaDB 10.1: ANALYZE statement
+5 Vote Up -1Vote Down

Last week, yours truly has pushed a new feature into MariaDB 10.1 tree: ANALYZE statement.

The idea of this feature is to make it easy to compare query plan with query execution. ANALYZE statement will run the statement, and produce EXPLAIN-like output, where optimizer’s estimates are followed by numbers that were observed when running the query. The output looks like this:


  • Next to rows there is r_rows column which shows how many records were read from the table.
  • Next to filtered there is r_filtered column which shows which fraction of …
  [Read more...]
Engineering behind EXPLAIN FORMAT=JSON (or lack thereof)
+3 Vote Up -3Vote Down

MySQL 5.6 has added support for EXPLAIN FORMAT=JSON. The basic use case for that feature is that one can look at the JSON output and see more details about the query plan. More advanced/specific use cases are difficult, though. The problem is, you can’t predict what EXPLAIN FORMAT=JSON will produce. There is no documentation or any kind of convention regarding the contents of JSON document that you will get.

To make sure I’m not missing something, I looked at MySQL Workbench. MySQL Workbench has a feature called Visual Explain. If you want to use, prepare to seeing this a lot:

In Workbench 6.1.4 you get it for (almost?) any query with subquery. In Workbench 6.1.6 (released …

  [Read more...]
The range access method and why you should use EXPLAIN JSON
Employee +0 Vote Up -0Vote Down

I got an interesting question about EXPLAIN and the range access method recently. The person had a query that could be written either with a BETWEEN predicate or an IN predicate, something similar to this:

-> FROM orders WHERE customer_id BETWEEN 7 AND 10 AND value > 500;
| id | select_type | table | type | key | key_len | rows | Extra
| 1 | SIMPLE | orders | range | cust_val | 10 | 91 | ... …

  [Read more...]
InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+1 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table - 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)

The query is:


This is a straight-forward query with no WHERE clause.

read more

Showing entries 1 to 10 of 58 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.