…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select…can be a lot of help for big scans. This will reduce the gap but…into several, run in parallel and aggregate result sets. So if you’re dealing with…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…. The scripts and results are on Benchmark Wiki. If we look on performance overhead it appears rather big in read-only benchmark… performance schema tables. 1. table_io_waits_summary_by_table mysql> select * from table_io_waits_summary_by_table where OBJECT_NAME….01 sec) 2. There is table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT…
Post: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
…MySQL, so I’ll try to be gentle. The following query will surprise users unpleasantly: select * from a where a.id in (select id from b); Users expect the inner query to execute first, then the results…. But it would be a big improvement if it were fixed. …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes …MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test) > select… in MySQL 5.6 innodb_file_per_table is now ON by default. This is very big change …
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT… after query is executed, so I could do something like: SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… give it? It’s pretty useful. It tells mysql to pipe the output of your commands through the specified… predictable I am sometimes, isn’t it?) mysql> pager less mysql> show innodb status\G For big result sets, it’s a pretty handy way… can we think of here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual-explain’ mysql> explain select * from sakila.film inner…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… to 2010′; mysql> use ontime1; Database changed mysql> show table status like… amount of data. One big reason for this is … the third parse time). $ echo “select count(*) from ontime_fact;” | ./run_query Array ( …_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… ROLLUP so I can get grouped result set together with total value for the groups: mysql> select grp, count(*) cnt from dt where… something to get the result set we want from single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt… forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same time…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…Spreadsheet so you can easy compare with previous results. There are different sheets for queries, datasize and…000.dir is 114G, which is as twice big as original data files. SHOW TABLE STATUS does… in summary table at the bottom) Query Q1: mysql> SELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it’…, or in MySQL 5.0 and earlier, doing the work on a slave, then piping the results back up to …The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick bad plans …
