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

Displaying posts with tag: optimizer (reset)

Better Performance for JOINs Not Using Indexes
Employee_Team +1 Vote Up -0Vote Down

In some cases it is not possible to use an index to optimize a JOIN. This may for example happen when you query the Performance Schema. As a result these kind of queries can be very slow; however in MySQL 5.6 and later you can use a trick to improve the performance considerably.

As a working example in this post, I will use the schema_table_statistics view in the sys schema. Since the view involves the schema, I will create a reasonable large number of databases and tables for the test:

shell$ for ((i=0; i<100; i++)); do
>    echo "Database ${i}"
>    mysql -e "CREATE DATABASE db${i}"
>    for ((j=0; j<100; j++)); do
>        mysql -e "CREATE TABLE
  [Read more...]
Q&A: Even More Deadly Mistakes of MySQL Development
+1 Vote Up -0Vote Down

On Wednesday I gave a presentation on “How to Avoid Even More 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 for

  [Read more...]
A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements
Employee_Team +2 Vote Up -0Vote Down

In MySQL, query optimization of single-table UPDATE/DELETE statements is more limited than for SELECT statements. I guess the main reason for this is to limit the optimizer overhead for very simple statements. However, this also means that optimization opportunities are sometimes missed for more complex UPDATE/DELETE statements.

Example

Using the DBT-3 database, the following SQL statement will increase prices by 10% on parts from suppliers in the specified country:

UPDATE part
SET p_retailprice = p_retailprice*1.10
WHERE p_partkey IN
     (SELECT ps_partkey
      FROM partsupp JOIN supplier
      ON ps_suppkey = s_suppkey
      WHERE s_nationkey = 4);

Visual EXPLAIN in MySQL Workbench (http://www.mysql.com/products/workbench/) shows that the optimizer will choose the

  [Read more...]
Re-factoring some internals of prepared statements in 5.7
Employee +1 Vote Up -0Vote Down
[ this is a re-posting of what I published on the MySQL server team blog a few days ago ]
 
When the MySQL server receives a SELECT query, the query goes through several consecutive phases:
  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains



  [Read more...]
Re-factoring some internals of prepared statements in 5.7
Employee_Team +5 Vote Up -0Vote Down

When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or outer joins are involved).
  • optimization: finding the best way to read tables: the best order of tables, and for each
  [Read more...]
Dynamic range access (and recent changes)
Employee_Team +2 Vote Up -0Vote Down

Dynamic range access (aka “Range checked for each record” since that is what EXPLAIN will say in the ‘Extra’ column) is one of the big mysteries to MySQL users. The reason is that it is used for queries that are less common, such as queries with non-equality join predicates. The following query is an example; it finds the number of messages sent since the user was last logged in:

EXPLAIN SELECT user.user_id, COUNT(message.id)
FROM message, user
WHERE message.send_date >= user.last_activity
GROUP BY user.user_id;

However, although such join predicates are less common, it doesn’t mean that the dynamic range access method is not important.

How the dynamic range access method works

It is easier to explain dynamic range access if we consider a “normal” join first, so let’s take a look at this

  [Read more...]
The MySQL Optimizer Cost Model Project
Employee_Team +3 Vote Up -0Vote Down

You may not be aware of this but the foundation that the MySQL optimizer builds on when choosing a query plan – the cost model – is for the most part very old. At least in tech terms.

Much of it was written in another millennium, at a time when “Forest Gump” and “Titanic” won Oscars and “Baywatch” was the big thing on TV. Although the revision history doesn’t go that far back, it wouldn’t come as a surprise if it predates that annoying “Macarena” song and even “The Sign” (Ace Of Base) – don’t follow those links unless you’re feeling very brave…

Thankfully, a lot has happened since Ace of

  [Read more...]
A new dimension to MySQL query optimizations – part 2
Employee_Team +0 Vote Up -0Vote Down

This is “A new dimension to MySQL query optimizations – part 2″. If you didn’t read part 1 already I suggest you skim through it before reading on.

To summarize, the problem at hand is this: Given a query with a join between two or more tables, the MySQL optimizer’s mission is to find the best combination of join order and access method so that the response time becomes as low as possible. The optimizer does this by calculating the cost of each combination and then picks the cheapest one.

Consider the following query:

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01"

The

  [Read more...]
A new dimension to MySQL query optimizations – part 1
Employee_Team +4 Vote Up -0Vote Down

It’s not radical to claim that one of the most important tasks of any DBMS query optimizer is to find the best join order for the tables participating in a query. Is it, e.g., better to read country or city first in

SELECT * 
FROM country JOIN city ON country.id=city.countryid 
WHERE city.population > 1000000 AND 
      country.region="EMEA"

employee or department first in

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01" AND
      department.location="Paris"

If the optimizer gets this wrong, the resulting response time may be disastrous (or hilarious, depending on your sense of humour).

Simply put (and ignoring some edge cases), the MySQL optimizer does the following to find the cheapest

  [Read more...]
Fun with Bugs #31 - what's new in MySQL 5.6.17
+1 Vote Up -0Vote Down
MySQL 5.6.17 will probably be announced loudly at or immediately before Percona Live MySQL Conference & Expo next week. But official release announcement via email was made on March 28, release notes and binaries to download are already available, so why not to check them carefully to find out what to expect from this 8th minor release of MySQL 5.6 GA...

First of all, it seems Oracle still does not hesitate to introduce new features and behavior in the process. Just check these major changes:
  • Starting with 5.6.17, MySQL now supports rebuilding regular and partitioned InnoDB tables using



  [Read more...]
Showing entries 1 to 10 of 76 10 Older Entries

Planet MySQL © 1995, 2014, 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.