… which allows you to limit size of MEMORY tables (the limit applies both to… tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1… hopeful future releases of MySQL, MariaDB or Drizzle bring improvements in this space allowing…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…selection why do we have value of 62 being default in MySQL 5.5 which can produce very expensive plan selections…MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…n_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see how… IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=… area of improvement in the optimizer, as it is clearly a part of the optimizer’s job to select the …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… improve join performance. As I told you above, when table t1 would be joined to table t2, then selected…orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let’s see … at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…on the MV. mysql> select mview$pk as rank, customer_id, total_price, total_lines from demo.dashboard_top_customers limit 10; +——+————-+————-+————-+ | …ORDER BY or use of NOW(), it but still provides improved performance during refresh by accessing summarized data. I hope …
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
…-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES userstat FALSE verbose TRUE wait-timeout 28800 Which is… good idea to extend SELECT syntax to ease querying of variable global value Right now I can select: mysql> select @@global.sort_buffer_size…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… so there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** …GROUP BY 1 ORDER BY c LIMIT 10; The next queries show how performance is improved when Shard-Query adds parallelism …
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…MySQL generates full rows while executing queries with limit …of these so performance improvements can be even higher. CREATE TABLE …mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
… rows: mysql> select ltrim_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql(author… paintings limit 100000; 100000 rows in set (0.17 sec) So for ltrim_junk() function we got almost 23x improvement and for…
