… allocated. This was not much of the problem in MySQL 3.23 when there would only handful of places where … can query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…of this benchmark, I have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB … query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o_shippriority from … the query time drops from 2534.41s down to under a minute. MySQL 5.6 has the smallest query time…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…sum(l_extendedprice * (1 – l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from…o_orderdate < date_add( '1993-08-01' ,interval '3‘ month) and l_returnflag = ‘R’ and c_nationkey…query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL…
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…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in set (0.18 sec) The query…query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query….095135 | 55.10 | 3 | 0…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…query would run on a cold MySQL instance, then it would require reading nearly 40 pages from…queries and shows the top queries with largest impact – longest sum of run time which typically (not always) shows queries… ======= ======= ======= ======= ======= ======= # Count 0 149 # Exec time 3 281ms …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1; +———–+ | sum(val) |…mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does …), pow(sum(rental_id), 17), pow(sum(rental_id), 18) from sakila.rental; I wrote a bash script to execute the query 500 times… query that returns the same result: select sql_no_cache pow(@sum := (select sum(rental_id) from sakila.rental), 1), pow(@sum, 2), pow(@sum, 3), pow(@sum…
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…. The results from the six branches are combined with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp…
