… aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now… in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for…` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr…, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_… or from Key-ordered Scan for TPCH query #3, in fact disabling both of these provided …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x This query…changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=…_orderdate < date_add( '1993-08-01' ,interval '3‘ month) and l_returnflag = ‘R’ and c_nationkey…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…demo mview_table_alias: c mview_join_condition: USING (customer_id) mview_join_order: 999 *************************** 3. row *************************** mview_table_id: 29 … items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query OK, 484 rows …
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem WHERE…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL… the test queries feature at least one join, and many of the filter conditions are placed…_id) WHERE Year BETWEEN 2009 and 2011 GROUP BY Carrier,dest.CityName ORDER BY 3 DESC; …
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…condition on c1, the view is accessed quicky: mysql> select c1, count(*) from v1 where…MySQL rewrites the view query to include the restriction: mysql> explain extended select c1, count(*) from v1 where…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… over 3 million “words” by duplicating the entire set multiple times. mysql> load data infile ‘/tmp/words’ into table words (chars); Query OK… database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3,1 desc…(*)`) Now, consider the query with BETWEEN 1 and 6 added to the where clause. This creates boundary conditions for our query. Any set of…
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
… where many applications can benefit a lot, in my experience. Most applications should simply retry these conditions…MySQL protocol level, which will increment Com_admin_commands in SHOW GLOBAL STATUS, or a trivial query… 1090.0772 7.5% 23621 0.0461 BEGIN # 3 0x6E85B9A9C9FF813E 868.0335 6.0% 6923 0…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
…is that triggers can not, under normal conditions, detect the commit order of transactions. … a second transaction: mysql> delete from test.demo where c1=1; Query OK, 1 row affected…id: 10 fv$server_id: 999 c1: 3 c2: 3 *************************** 3. row *************************** dml_type: -1 uow_id…
