Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 70 Next 10 Older Entries

Displaying posts with tag: optimizer (reset)

When kill flag is checked for SELECT? Part I
+2 Vote Up -0Vote Down
Manual describes this briefly:

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

Complete, correct and useful answer is more complex though. Here is correct answer, but not very useful. So, kill flag is checked in the following functions related to SELECT statement processing:

make_join_statistics()
best_extension_by_limited_search()
find_best()
sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()











  [Read more...]
Cost-based choice between subquery materialization and EXISTS
Employee +7 Vote Up -1Vote Down
In a previous post, I had demonstrated how subquery materialization, introduced in MySQL 5.6.5, improves the performance of certain queries, like query Q16 of DBT3. Such improvement was easily explained:
  • Subquery materialization has a high start up cost (it needs to create and fill the temporary table).
  • But afterwards it has fast lookups (temporary table has a hash index, no duplicates, and is in memory).
  • In other words, compared to EXISTS, the first evaluation of the IN predicate is slow (high start up cost) and all following evaluations are fast (just a hash lookup).
  • In the DBT 3 setup, one outer table (named "part") has 200,000 rows, so there

  [Read more...]
The Optimiser Conundrum
+2 Vote Up -0Vote Down

We’ve been helping a long-term client who runs some fairly complex queries (covering lots of tables and logic on a respectably big but mainly volatile dataset). We tend to look first at query structure and table design, as fixing problems there tends to have the most impact. This contrary to just tossing more hardware at the problem, which is just expensive.

As subqueries are used (and necessary in this case), MariaDB 5.3 was already a great help with its subquery optimisations. Once again thanks, Monty and the Monty Program optimiser team (Igor, Sergey, Timour, and possibly others) – all former colleagues and they’re absolutely awesome. Together, they know the MySQL optimiser like no other.

Because the queries are generated indirectly from an exposed API (just for paying clients, but still), the load is more unpredictable than having merely a local front-end. Maintaining

  [Read more...]
Join Optimizations in MySQL 5.6 and MariaDB 5.5
+1 Vote Up -0Vote Down
This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5
Faster subqueries with materialization
Employee +3 Vote Up -0Vote Down
In a previous post, I analyzed how a query of the famous DBT3 benchmark was
optimized by MySQL. It was this query, named "Q16" in the DBT3 jargon:

select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#23'
and p_type not like 'LARGE PLATED%'
and p_size in (43, 1, 25, 5, 35, 12, 42, 40)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size;

































  [Read more...]
Join Optimizations in MySQL 5.6 and MariaDB 5.5
+2 Vote Up -0Vote Down

This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 has introduced some additional optimizations which we will also look at, in this post.

Now let me briefly explain these optimizations.

Batched Key Access

Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins are read, and then for each of these rows an index lookup is performed on the second

  [Read more...]
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
+0 Vote Up -0Vote Down
I have written a second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
+3 Vote Up -1Vote Down

This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look at what this optimization actually is and what benefits it brings.

Multi Range Read

With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for column values that do not

  [Read more...]
Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
+2 Vote Up -0Vote Down
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5
Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
+1 Vote Up -0Vote Down

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at.

Index Condition Pushdown

Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example,

  [Read more...]
MariaDB 5.3 query optimizer by Sergey Petrunia
+3 Vote Up -0Vote Down

Sergey Petrunia of the MariaDB project.

What exactly is not working in MySQL? MySQL is poor at decision support/analytics. With large datasets you need special disk access strategies. Complex queries like insufficient subquery support and big joins are common int he MySQL world.

DBT-3 is used, scale=30, with a 75GB database and run a query “average price of item between a range of dates”. Query time took some 45 minutes to execute. Why? Run iostat -x to see what is going on. See that the CPU is mostly idle, so its an IO-bound load. Next you run SHOW ENGINE INNODB STATUS and you’ll see how many reads per second is happening. Possible solution is to get more RAM or get an SSD (good to speedup OLTP workloads, but analytics over data is probably not

  [Read more...]
MySQL 5.6.4 Development Milestone Now Available!
Employee_Team +8 Vote Up -0Vote Down

I am pleased to announce that the MySQL Database 5.6.4 development milestone release ("DMR") is now available for download (select the Development Release tab). MySQL 5.6.4 includes all 5.5 production-ready features and provides an aggreation of all of the new features that have been released in earlier 5.6 DMRs.  5.6.4 adds many bug fixes and more new "early and often" enhancements that are development and system QA complete and ready for Community evaluation and feedback.  You can get the complete rundown of all the new 5.6.4 specific features here.

For those following the progression of the 5.6 DMRs as the trains leave the station, you should

  [Read more...]
MySQL 5.6.4 Development Milestone Now Available!
Employee_Team +0 Vote Up -0Vote Down

I am pleased to announce that the MySQL Database 5.6.4 development milestone release ("DMR") is now available for download (select the Development Release tab). MySQL 5.6.4 includes all 5.5 production-ready features and provides an aggreation of all of the new features that have been released in earlier 5.6 DMRs.  5.6.4 adds many bug fixes and more new "early and often" enhancements that are development and system QA complete and ready for Community evaluation and feedback.  You can get the complete rundown of all the new 5.6.4 specific features here.

For those following the progression of the 5.6 DMRs as the trains leave the station, you should

  [Read more...]
More Early Access Features in the MySQL 5.6.3 Development Milestone!
Employee_Team +0 Vote Up -0Vote Down

For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

  [Read more...]
More Early Access Features in the MySQL 5.6.3 Development Milestone!
Employee_Team +2 Vote Up -0Vote Down

For those with an interest in MySQL, this week at Oracle OpenWorld has gotten off to a great start.  Demonstrating how Oracle drives MySQL innovation Tomas' "State of the Dolphin" keynote on Monday gave a great overview of the new MySQL products that have recently been delivered:

  [Read more...]
More on OR-conditions considered bad... And an apology..
+4 Vote Up -0Vote Down
In my recent post on OR-conditions I made a mistake, and I appologize for that. I made the statement that MySQL will only use 1 index per statement, whatever you do.

This is no longer true, as a matter of fact, and that has been the case since MySQL 5.0 and I should have checked. MySQL is actually able to use index_merge. An explanation why I didn't look for thi more carefully, yes an explanation, not an excuse, is that the optimizer doesn't seem to want to use this very often. Which is too bad.

So, with this in mind, and using the same table as in the previous post, let's look at index_merge in action. Or possibly, not so much in action. Let's recap what the table looks like:
CREATE TABLE `product` (
`id` int(11) NOT NULL





  [Read more...]
What does Handler_read_rnd mean?
+3 Vote Up -0Vote Down

MySQL's SHOW STATUS command has two counters that are often confusing and result in "what does that mean?" questions:

  • Handler_read_rnd
  • Handler_read_rnd_next
  • As I understand it, there is some historical context to the choice of names here, hearkening back to before I was involved with MySQL, way back when it was a wrapper around ISAM tables -- or even earlier, I don't know. (Unireg, anyone?) In any case, these two variables deserve a little explanation.

    Both counters indicate the number of times the corresponding storage engine API function has been called. In olden times, the storage engine API was called the handler API, which is why the variables begin with Handler_.

    Handler_read_rnd counts the number of times the handler::rnd_pos() method is called. This method fetches a row from a table based on a "fixed position," i.e. a

      [Read more...]
    EXPLAIN EXTENDED can tell you all kinds of interesting things
    +2 Vote Up -0Vote Down

    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 query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process is called query rewriting, and it is a part of any good SQL optimizer. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including the rewritten query.

    To take a look at EXPLAIN EXTENDED, I'll start with three empty tables. It is important to note that the tables are empty because the MySQL optimizer treats empty tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a

      [Read more...]
    Extending Index for Innodb tables can hurt performance in a surprising way
    +2 Vote Up -0Vote Down

    One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not the case.

    PLAIN TEXT SQL:
  • CREATE TABLE `idxitest` (
  •   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  •   `a` int(11) NOT NULL,
  •   `b` int(11) NOT NULL,
  •   PRIMARY KEY (`id`),
  •   KEY `a` (`a`)
  • ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1
  •  
  • mysql> SELECT count(*) FROM idxitest
  •   [Read more...]
    A workaround for the performance problems of TEMPTABLE views
    +0 Vote Up -0Vote Down

    MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which uses the TEMPTABLE algorithm will have to compute the entire contents of the view before any filter is applied. Computing the entire contents requires a temporary table and many more rows may be accessed than otherwise would had the filter been merged into the view query.

    As long as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use the MERGE algorithm. Unfortunately, this means that views of any significant complexity will almost always use the TEMPTABLE algorithm.

    This blog post

      [Read more...]
    Joining on range? Wrong!
    +4 Vote Up -0Vote Down

    The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.

    To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:

    PLAIN TEXT SQL:
  • CREATE TABLE `products` (
  •   `prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  •   `prd_name` varchar(32) NOT NULL,
  •   [Read more...]
    When the subselect runs faster
    +1 Vote Up -1Vote Down

    A few weeks ago, we had a query optimization request from one of our customer.

    The query was very simple like:

    PLAIN TEXT CODE:
  • SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
  • This column in the table is looks like this:

    PLAIN TEXT CODE:
  • `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL
  • The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

    PLAIN TEXT CODE:  [Read more...]
    MySQL University: Optimizing Queries with EXPLAIN
    Employee +1 Vote Up -0Vote Down

    This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today.

    For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions are
      [Read more...]
    MySQL University: Optimizing Queries with EXPLAIN
    Employee +0 Vote Up -0Vote Down

    This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today.

    For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions
      [Read more...]
    MySQL University: Optimizing Queries with EXPLAIN
    Employee +0 Vote Up -0Vote Down

    This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today.

    For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University
      [Read more...]
    Getting around optimizer limitations with an IN() list
    +1 Vote Up -0Vote Down

    There was a discussion on LinkedIn one month ago that caught my eye:

    Database search by "within x number of miles" radius?

    Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
    Thankful for any tips you can throw my way..

    J

    A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:


      [Read more...]
    Quick comparison of MyISAM, Infobright, and MonetDB
    +4 Vote Up -0Vote Down

    Recently I was doing a little work for a client who has MyISAM tables with many columns (the same one Peter wrote about recently). The client's performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they're ad-hoc (can access any columns) and it seems tailor-made for a column-oriented database.

    I decided it was time to actually give Infobright a try. They have an open-source community edition, which is crippled but not enough to matter for this test. The "Knowledge Grid" architecture seems ideal for the types of queries the client runs. But hey, why not also try

      [Read more...]
    Guidance for MySQL Optimizer Developers
    +5 Vote Up -2Vote Down

    I spend large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last 10 years I chased first Monty and later Igor with Optimizer complains and suggestions. Here are some general ideas which I think can help to make optimizer in MySQL, MariaDB or Drizzle better.

    Make it Plugable Every Major optimizer change causes a lot of pain for some of the users. Even if you improve performance 99 our of 100 queries there are going to be countless users complaining about the change. Due to this problem Optimizer Team was more conservative than I think they could have been. The solution is simple - make optimizer pluggable and make it possible to stick to old optimizer behavior with new MySQL Version.

    Make Cost Model Adjustable MySQL Optimizer

      [Read more...]
    Multi Column indexes vs Index Merge
    +3 Vote Up -0Vote Down

    The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE='CA' they would create 2 separate indexes on AGE and STATE columns.

    The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case.

    MySQL indexes are (with few exceptions) BTREE indexes - this index type is very good to be able to quickly lookup the data on any its prefix and traversing ranges between values in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until it runs into the

      [Read more...]
    Explaining subqueries in the FROM clause
    +0 Vote Up -0Vote Down
    I was debugging the performance of a DELETE statement that contained a subquery in the FROM clause. As there is no EXPLAIN for DELETE, I converted it to a SELECT statement (and hoped the same optimizations were done). But I still had to wait for EXPLAIN to complete. EXPLAIN evaluates subqueries in the FROM clause for MySQL. This can make EXPLAIN take a long time and create load on a server. Recent versions of MySQL have had many improvements for subquery optimization, but the documentation for all versions states that this is still done. A feature request is open to change this. Feature requests are also open to get EXPLAIN for UPDATE, INSERT and  [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 70 Next 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.