… drawback, Batched Key Access (BKA) optimization was introduced. When BKA is being used then, after the selected rows are read from table1… as probe step. This join algorithm works best for highly selective queries, and obviously when the first operand used in the… read more about these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… number of tables. I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL 5.5 which can produce very expensive plan selections ? Investigating this further I found the following explanation from Timour… from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic selection works…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x… area of improvement in the optimizer, as it is clearly a part of the optimizer‘s job to select the best query execution plan. I had noted a similar thing when benchmarking ICP, the optimizer made…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general… means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this… WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but resolving server gridlock…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
I looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason … index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone…
Post: Webinar "How to Turbocharge Your MySQL Performance Using Flash Storage"
March 13, 2012 By 5 Comments
…: Configuration and optimization techniques to fully leverage flash-based storage solutions in MySQL environments Evaluation criteria and techniques for selecting the suitable… Approaches for scaling MySQL instances on fewer servers while delivering optimal performance using flash drives The registration is free and available…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
February 27, 2012 By 53 Comments
Here is a beautiful solution; SELECT COUNT(*) FROM (SELECT 1 FROM test WHERE some_conditions LIMIT any_no) alias_table; This results count of total rows wihtout any optimizations.
Comment: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 11, 2012 By 2 Comments
@Igor, Yes you are right the original query does not have an index hint, but I have had to make the QEP selected by the optimizer consistent otherwise it would keep flipping. I would post the numbers for the query without the index hint soon.
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I think it is worth to note even though ICP improves the scan speed of the range in the index by allowing scanning index entries only and not reading the rows, it still scans complete range when partial scan is possible. In the example above “l_partkey = x and l_quantity >= 1 and l_quantity
