… the subset sum check from SQL, which you can then distribute with Shard-Query: CREATE TABLE `the list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `val` bigint(20) NOT NULL DEFAULT ’0′, PRIMARY KEY… will cause our check to pass: mysql> insert into data (val) values (16); Query OK, 1 row affected (0.01 sec) mysql> SELECT val…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…check that it parses by running the SELECT portion, adding LIMIT 0 to the SELECT part of the query…not take long to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’, -> ‘dashboard_customer_sales’),’BOTH’,NULL); Query…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…query with the join in both directions. Explain for query Q1.1: mysql> select straight_join sum…1 | | 4 | checking…NOT EXISTS lineorder ( LO_OrderKey bigint not null, LO_LineNumber tinyint not null, LO_CustKey int not null, LO_PartKey int not null, LO_SuppKey int not null, LO_OrderDateKey int not null…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum…NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…NOT NULL DEFAULT ’0′ ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +——-+—————————————————————————————————–+ 1 row in set (0.00 sec) mysql…check to see if any two numbers sum to zero: mysql…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql> select count(*) from m; +———-+ | count(*) | +———-+ | 1047684 | +———-+ 1 row in set (0.00 sec) mysql…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…queries that execute slower in new MySQL 6.0 version. Query is pretty simple SELECT sum…NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` char(25) NOT NULL, `l_comment` varchar(44) NOT NULL, `l_shipmode` char(10) NOT NULL…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…NOT NULL, PRIMARY KEY (`id`), KEY `d` (`d`,`group_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18007591 DEFAULT CHARSET=latin1 mysql> select sum… IN list in this query: mysql> explain select sum(events) from info where … does not work for multiple group remains question to check with MySQL Optimizer team 
…
