…is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and …_orderdate >= ’1993-08-01′ and o_orderdate < date_add( ’1993-08-01′ ,interval ’3′ month) …query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for MySQL…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…matching the date range. A very inefficient approach! MySQL was unable to optimize those simple conditions to match both product id and date range by index…itm_order_date (itm_prd_id, itm_order_date); UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp); Now the rewritten query: …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…queries and b) choose your primary key correctly. Let’s go back to the ad-by-day table. If you query date ranges… all that fast on big joins; the query optimizer can sometimes pick bad plans when you …-box than MySQL is, especially for more complex queries. Percona is not tied to MySQL, although we’…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query_prealloc_sizeblogpercona.commanual Questionsblogpercona.commanual rand_seed1blogpercona.commanual rand_seed2blogpercona.commanual range…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… and suboptimal execution for large ranges. Lets take a look at very typical reporting query which queries data for date range for multiple of groups… have something to learn about MySQL Optimizer gotchas 
At the same time I figured out how to make MySQL Optimizer to do what we…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…this case index range scan will be started and query execution stopped as… networking and dating sites are perfect example of such queries SELECT FROM … if needed In some cases MySQL Optimizer may prefer to use different …query log as query which does not use indexes, even if it is quite fast: mysql…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
…_date; (Pop quiz: do I need SQL_NO_CACHE on this query?) The average execution time for this query is 7.96, ranging from…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10… to make your queries faster — you usually can’t optimize the WHERE clause itself, only the way that the MySQL optimizer chooses to apply…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…password` varchar(64) NOT NULL default ”, `dob` date default NULL, `address` varchar(128) NOT …MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql…query. Falcon scales pretty bad and there is a big room for optimization. READ_PK_RANGE Query…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…optimizer, which should affect execution plan of TPC-H queries. In reality only two queries…MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem WHERE l_shipdate >= date… | 1 | SIMPLE | lineitem | range …
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…- why range which covers 5 rows is hundreds of times slower than single row ? If MySQL Optimizer would …a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name…is a bit artificial. You would probably use date (or at least year) or birth instead of…
