…in both MySQL 5.6 and MariaDB 5.5. You can read more about BKA in MySQL 5.6 here and BKA in…and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; In…in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…sales dsc ‘> group by customer_id ‘> order by total_price desc; ‘> ‘); Query OK, 1 …And then confirm they contain the same results mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…same as if you didn’t take it apart. Projection, GROUP BY, SUM, COUNT, MIN*, and…, but right now only MySQL storage nodes are supported….and other databases, all at the same time, and transparently to the source query…) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… MySQL command line client was used to execute the a script file containing the 11 queries. This same SQL script was used in the… 2000 AND 2008 GROUP BY 1 ORDER BY c LIMIT 10; The next queries show how performance is improved when Shard-Query adds parallelism when “subqueries in the…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
…queries: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by…group by grp with rollup order by cnt desc limit 11; ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…ORDER BY K.ORDINAL_POSITION), ‘) = (‘, GROUP_CONCAT(CONCAT(K.REFERENCED_TABLE_NAME, ‘.’, K.REFERENCED_COLUMN_NAME) ORDER BY…and Bar. If we run this query and it returns a non-empty result set, it shows us which rows in Bar are orphans. mysql…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…QUERY_ID = @query_id GROUP BY STATE ORDER BY…by the profiling). That is, the SUM(DURATION) isn’t the same as the total query duration. Alas, there is no query…in the profile. The above query is something I developed for High Performance MySQL Third Edition, by the way. The book should be available in…
Post: Power of MySQL Storage Engines
March 5, 2007 By 3 Comments
…same problem with Sphinx – Sphinx can run sorting and group by operations much faster than MySQL can and it can do it in…order by and group by functions so it is implemented in rather hackish way. Same applies to checking number of rows in result set and…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…Query Q2: mysql> SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay>10 AND Year BETWEEN 2000 AND 2008 GROUP BY DayOfWeek ORDER BY c DESC; 7 rows in…
