…ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select i,count(*) cnt from trunc group by i order by cnt desc limit 10;…i | i | 4 | const | 81602 | Using where; Using index | +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ 1 row in set, 1 …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…group by 1,2 ) sq order by pct_buffer_pool desc; +——————-+——————+——-+——+——+———+—————–+ | table_name | index_name | …
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… rows with mostly unique “c” column. mysql> explain select count(*) cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type… large enough to fit all key blocks from temporary table index, so we get a lot of key writes which kill…) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt group by c order by…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… total number of tables, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000…, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…” the table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group by val; Query OK, 9… no index on 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: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_counts… just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from user_counts uc, user_counts uc2 where uc.user…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1; mysql> SELECT count(*), status FROM sales GROUP by status; +———-+———+ | count(*) | status | +———-+———+ |   65536 | …case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by…MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey GROUP BY… clause on an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is…_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE s_nationkey=’24′ GROUP BY s_nationkey, l…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY…by index COUNT(*) query can cause a lot of troubles. Imagine for example some sort of profile browsing at social networking site – SELECT COUNT…
