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

Displaying posts with tag: optimizer (reset)

on ORDER BY optimization
+1 Vote Up -0Vote Down

Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. In our major user database environment 99.9% of queries don’t have alternative query plans available (either because of forced indexes or just straightforward Primary Key read). We have various other systems and from time to time we have to do SQL work there and chase optimizer errors.

There’re multiple places where optimizer can make a choice in very basic queries, for example:

  • Which index returns less rows
  • Which index can be used for ORDER BY

A query that I was looking asked a very basic question, on a job …

  [Read more...]
WITH RECURSIVE and MySQL
Employee +3 Vote Up -1Vote Down

If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called "WITH clause" of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression.
In its simplest form, this feature is a kind of "boosted derived table".

Assume that a table T1 has three columns:


CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ... …






  [Read more...]
MySQL indexing 101: a challenging single-table query
+1 Vote Up -0Vote Down

We discussed in an earlier post how to design indexes for many types of queries using a single table. Here is a real-world example of the challenges you will face when trying to optimize queries: two similar queries, but one is performing a full table scan while the other one is using the index we specially created for these queries. Bug or expected behavior? Read on!

Our two similar queries

# Q1
mysql> explain select col1, col2 from t where ts >= '2015-04-30 00:00:00'; …
  [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...]
When ONLY_FULL_GROUP_BY Won’t See the Query Is Deterministic…
Employee_Team +1 Vote Up -0Vote Down

This is a follow-up post to my recent announcement of only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).

In the SQL standard, GROUP BY should contain only table columns and never expressions. So this example query where we want to count how many people have the same name is illegal in the SQL2011 standard because the GROUP BY clause contains a function:

  [Read more...]
Some Notes on Index Statistics in InnoDB
Employee_Team +1 Vote Up -0Vote Down

In MySQL 5.6 we introduced a huge improvement in the way that index and table statistics are gathered by InnoDB and subsequently used by the Optimizer during query optimization: Persistent Statistics. Some aspects of the way that Persistent Statistics work could be improved further though, and we’d really like your input on that.

How much to sample?

The statistics are gathered by picking some pages semi-randomly, analyzing …

  [Read more...]
MySQL 5.7: only_full_group_by Improved, Recognizing Functional Dependencies, Enabled by Default!
Employee_Team +2 Vote Up -0Vote Down

Here’s an account of recent work which has kept me busy and excited for a few months. For those unfamiliar with the only_full_group_by sql mode, let me provide some context. I’ll use the world database, which can be downloaded from this MySQL Documentation page. You can find details on how to install the database on the same page. We have a table of countries and a table of languages spoken in each country. It’s a 1-to-N relationship: …

  [Read more...]
Write Yourself a Query Rewrite Plugin: Part 2
Employee_Team +1 Vote Up -0Vote Down

In my last post I covered how to use the query rewrite framework to write your own pre-parse plugin. The interface is simplistic: a string goes in, a string comes out, and the rest is up to your plugin’s internal workings. It doesn’t interact that much with the server. Today I am going to show you the other type of plugins that rewrite queries, post-parse query rewrite plugins. This type is, out of necessity, more tightly coupled with the server; it operates on the internal data structures that make up the query’s parse tree.

…  [Read more...]
Write Yourself a Query Rewrite Plugin: Part 1
Employee_Team +1 Vote Up -0Vote Down

With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.

When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I cover in a separate post

  [Read more...]
Generated Columns in MySQL 5.7.5
Employee_Team +0 Vote Up -1Vote Down

Generated Columns is a new feature available in the latest lab release. This work is based on a contribution by Andrey Zhakov. Thanks, Andrey! The Optimizer team modified it to follow the current MySQL design, and to lift a number of limitations.

The syntax is:

<type>   [ GENERATED ALWAYS ]   AS   ( <expression> )   [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ]   [ [PRIMARY] KEY ]   [ NOT …

  [Read more...]
Showing entries 1 to 10 of 87 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.