… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval…=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx…=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… possibly, the number of distinct values in that column. You see, there’s another form for COUNT(): select count(distinct col1) from table; So when I see a query that just does COUNT(col1) I am… think it does. You meant to count the number of distinct values, but you left out the DISTINCT keyword. That’s also a…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in set (0.02 sec) Each year has tens of millions of flights: mysql> select count…
Comment: Why MySQL could be slow with large tables ?
April 12, 2007 By 141 Comments
….answerID, A.answername, A.answervalue, COUNT(DISTINCT e3.evalanswerID) AS totalforthisquestion, COUNT(DISTINCT e1.evalanswerID) AS totalforinstructor, (COUNT(DISTINCT e3.evalanswerID)/COUNT(DISTINCT e1.evalanswerID)*100) AS answerpercentage….answerID, A.answername, A.answervalue, COUNT(DISTINCT e3.evalanswerID) AS totalforthisquestion, COUNT(DISTINCT e1.evalanswerID) AS totalforinstructor, (COUNT(DISTINCT e3.evalanswerID)/COUNT(DISTINCT e1.evalanswerID)*100) AS answerpercentage…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…: mysql> select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select count(distinct i) from antest; +——————-+ | count(distinct i) | +——————-+ | 101 | +——————-+ 1 row in set (0.20 sec) mysql> select count(distinct i,c) from antest; +———————+ | count(distinct i…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… ? Let me show. The query in question is (Q1): SELECT count(distinct username) FROM tracker where TIME_ID >= ’2009-07-20 00…, as we made key “block_id” inserted not in order. +—————————+ | count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92…
Comment: Speeding up GROUP BY if you want aproximate results
March 8, 2008 By 24 Comments
… ? Here is example: mysql> select count(distinct page) from (select page from performance_log_080306 limit 100000) x; +———————-+ | count(distinct page) | +———————-+ | 83102 | +———————-+ 1 row in set (0.65 sec) mysql> select count(distinct crc32(lower(page))) from (select…
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 45 Comments
… 0 there’s only 3 distinct values in the table for this column, so its not actually counting the values but the pointers from this index: mysql> select count( distinct (comment_approved) ) from wp_comments; +————————————–+ | count( distinct (comment_approved…
Comment: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
input: select a expr1, count(distinct b) expr2, count(*) expr3 from a join b where a.id = b.a_…, count(*) expr3 from a join b where a.id=b.a_id 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), etc are handled…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…*** Y *All aggregate functions supported, except GROUP_CONCAT and AVG(distinct). **non-deterministic functions like RAND(),NOW(), etc, are not supported… use MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values for each…
