… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval… –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct… –mysql-socket=/tmp/mysql.sock –oltp-point-selects=9 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> …MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero. It looks simple… because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of… optimizing ? – see how many rows query sends after group by, distinct and aggregate functions are removed (A) – look at number of…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… 29 air 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(*) from…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… with COUNT(). This is database-agnostic, not related to MySQL. The problem is when the COUNT() contains a column name, like this: select count… possibly, the number of distinct values in that column. You see, there’s another form for COUNT(): select count(distinct col1) from table; So…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that logging all queries… shown here that every query is reading approximately 38.53 distinct InnoDB pages (meaning 616.48K of data), however, 95% of…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where val in (-2,-3,-10,15,15,16) group by val ; +—–+——————–+ | val | count(distinct id…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
… (`id`), KEY `id` (`id`) ) ENGINE=MyISAM; SELECT val as `val`, COUNT(DISTINCT (id)) as `cd` FROM test.data as d WHERE val… pass the check. There are enough 15s though. The distinct value count for each item in the output set, must at least… pass: mysql> insert into data (val) values (16); Query OK, 1 row affected (0.01 sec) mysql> SELECT val as `val`, COUNT(DISTINCT (id…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will not write into slow log any…: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest where id… No InnoDB statistics available for this query SELECT COUNT(1) FROM t2; Important note: Although the…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… I simply created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select…_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by…
