… test. This VM was rebooted between tests. A SQL script was fed to the run_query.php script and the output was…>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) WHERE DepDelay>10…_date using (date_id) WHERE DepDelay>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… | grep “10.”| grep -v internal |tee hosts.internal [host list omitted] Now you need to…parse time). $ echo “select count(*) from ontime_fact;” | ./run_query Array ( [count(*)] => 135125787 ) 1 rows returned… cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] 12359 $ tail -…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…` char(1) DEFAULT NULL, `o_totalprice` decimal(10,2) DEFAULT NULL, `o_orderDATE` date DEFAULT …rows in the supplier table (left operand) to join to the lineitem table (right operand) …SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT o.*, count…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…to do that, so I generated big SQL…Count flights per day from 2000 to 2008 years SELECT DayOfWeek, count…
Comment: Finding out largest tables on MySQL Server
October 6, 2008 By 27 Comments
…to make it interesting, I will to the show table status and your SQL statement and get different results from the count…length+index_length DESC LIMIT 10; +———————————————-+——–+——-+——-+————+———+ | concat(table_schema,’.',table_name) | rows | …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… spent counting the rows which are already traversed and counted for group by operation. The obvious optimization is to get rid of LIMIT 10… only thing it needs to do is maintain an extra count. So if MySQL does not allow us to use ORDER BY together… I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…———- SEMAPHORES ———- OS WAIT ARRAY INFO: reservation count 13569, signal count 11421 –Thread 1152170336 has waited …@ ;; 3: len 10; hex 61202020202020202020; asc a ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:… 728899 localhost root Sending data select sql_calc_found_rows * from b limit…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…. It seems LucidDB has to scan whole table to get result. – Q0: select avg(c1) from (select “Year”,”Month”,count(*) as c1 from….9 sec – Q3: !set rowlimit 10 SELECT “Origin”, count(*) AS c FROM otp.”ontime” WHERE “DepDelay”>10 AND “Year” BETWEEN 2000 AND 2008…, transform external data (there is even access to MySQL via JDBC driver) just in SQL queries (compare with single LOAD DATA statement…
Post: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
… MySQL 5.6.10 source, in file sql/log.cc. I discovered that they have refactored the way they write to file-based… slow query information to the log table, query times and lock times are truncated to to integers: sql/log.cc, Log_to_csv_event_handler… are rounded down to the nearest whole second, meaning any query that executes in less than 1.0 seconds counts as 0…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
Hi, Here is an easy way to run the subset sum check from SQL, which you can then distribute with Shard-Query… val as `val`, COUNT(DISTINCT (id)) as `cd` FROM test.data as d WHERE val in (-2,-3,-10,15,15,16… numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10 | 1 | | -3 | 1 | | -2 | 1 | | 0 | 250000…
