… the size of the hash table built on it is greater than the join_buffer_size, then multiple hash tables would be… output as returned by MySQL 5.5 is as follows: SELECT o.*, count(*) as num_items FROM orders AS o INNER JOIN… the left operand which is the table orders are far greater than the size of the join buffer. The size of the…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…a greater frequency than 45 minutes, even as frequently as every minute, perhaps. The refresh method you select effectively…than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… time period remember to set it back to a value greater than 0, because otherwise you can have a really large log…_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare that to Percona Server with log…_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that logging all queries in this…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |     239 | +———-+ 1 row in set (0.00 sec) mysql> SELECT count(distinct(population)) FROM Country; +—————————–+ | count…too many countries have a population greater than 1000. An automated search for…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… only comes in to play when innodb_thread_concurrency is greater than zero. On a stock install of MySQL, here are some…) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0.00…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…5.2pl1 32bit. Requires m1.small or greater. snap-073b6e68 shard-query-demo-data-flatfiles… column store performs about 8x-10x better than a row store for queries which … third parse time). $ echo “select count(*) from ontime_fact;” | ./run_query Array ( [count(*)] => 135125787 ) 1 rows returned (…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=…02s. But the query times for MariaDB 5.5 are greater than both MySQL 5.5 and MySQL 5.6 IO ….6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had …
