…, start suffering from the trx_list overhead created by concurrent updates. Once we step away from this… innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read_…oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk… sum(cnt) from (select count(*) cnt from performance_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031 | +———-+ 1…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… the view query. As long as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use the MERGE algorithm. Unfortunately…) AS `count(*)` from `test`.`t2` where (`test`.`t2`.`c1` = 10) group by `test`.`t2`.`c1` | +——-+——+———————————————————————————————————————————+ 1 row in set (0.00 sec…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… Year BETWEEN 2001 and 2001 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.1 SELECT dest.CityName, COUNT( DISTINCT origin.CityName) from ontime… Year BETWEEN 2001 and 2005 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.2 SELECT dest.CityName, COUNT( DISTINCT origin.CityName) from ontime… Year BETWEEN 2001 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.3 SELECT dest.CityName, COUNT( DISTINCT origin.CityName) from ontime…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… JOIN demo.order_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+ | customer_id |…_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values for each group…
Comment: Why MySQL could be slow with large tables ?
April 12, 2007 By 141 Comments
… COUNT(DISTINCT e3.evalanswerID) AS totalforthisquestion, COUNT(DISTINCT e1.evalanswerID) AS totalforinstructor, (COUNT(DISTINCT e3.evalanswerID)/COUNT(DISTINCT e1….e3.questionid = Q.questionID AND e3.answerID = A.answerID GROUP BY Q.questionID, Q.questionsetID, Q.question, ASets.answersetid…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… from ontime WHERE DepDelay>10 GROUP BY YearD), t2 as (select YEARD,count(*) as c2 from ontime GROUP BY YEARD) select t.YEARD, c1… from ontime WHERE DepDelay>10 GROUP BY YearD) t JOIN (select YEARD,count(*) as c2 from ontime GROUP BY YEARD) t2 ON (t.YEARD… years. SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE Year BETWEEN N and M GROUP BY DestCityName ORDER BY 2 DESC LIMIT 10; Years…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…> ’1996-05-01′ GROUP BY o_orderkey ORDER BY num_items DESC LIMIT 10; …from the table lineitem for each distinct key value, in this test case…
Comment: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… a expr1, count(distinct b) expr2, count(*) expr3 from a join b where a.id = b.a_id group by a; rewrite (these… group by a,b; final query: select expr1, count(distinct expr3) expr2, sum(expr3) expr3 from #tmp group by expr1; Any COUNT(distinct), STDDEV,VARIANCE,SUM(DISTINCT…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
… `val`, COUNT(DISTINCT (id)) as `cd` FROM test.data as d WHERE val in (-2,-3,-10,15,15,16) GROUP BY val… numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10 | 1 | | -3 | 1 | | -2 | 1 | | 0… `val`, COUNT(DISTINCT (id)) as `cd` FROM test.data as d WHERE val in (-2,-3,-10,15,15,16) GROUP BY val…
