… query executed on the InnoDB table: SELECT non_key_column FROM… If the buffer size is large enough only a single range …group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order…MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… for OS cache to keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c… IO. Clearly OS write caching is not effective for relatively large area (which still fits in memory) which is getting a…) 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: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count…GROUP BY Year, Carrier,dest.CityName ORDER BY 4 DESC; Conclusion The divide and conquer approach is very useful when working with large…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…perspective, one must run a function on a large amount of input. Often times, the same …interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3…to create six hash “buckets” in the table. This allows MySQL to set up a sequential scan over…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…. Here are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY cnt DESC limit 10, the… perform group by so for large number of rows you’ve simply got to use different solutions, such as summary table. With MySQL 5… queries do not need GROUP BY. It can be reachable by normalization or other techniques. COUNT Many people think counting is fast, some…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… table as I used for MySQL Group by Performance Tests to see how much MySQL can …can see from this show status output MySQL only counts completely sorted rows in Sort_rows…large sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order…
Post: Are you designing IO bound or CPU bound application ?
December 20, 2006 By 10 Comments
… obvious but it is extremely important for MySQL Performance Optimization. In fact I probably have … a lot of rows are analyzed – count queries, group by, order by without indexes, search queries etc. … and these rows are “random access” to large tables (so chances they would require physical IO are…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
…which each examine large amounts of data. Since a single query is single threaded in MySQL, the …aggregation (count(*)) on a range of records. This table is partitioned by month which means that MySQL can … AND ’2008-12-31′ GROUP BY DayOfWeek ORDER BY c DESC; Q3 – SELECT Origin, count(*) AS c FROM …
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
…large users” as users having more than 100.000 images uploaded and measure the response time for them specifically because this group…mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql…order…
Post: Just do the math!
July 6, 2009 By 4 Comments
… you have query like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY cnt DESC LIMIT 100″ to … the temporary table – the number of rows MySQL can insert/update depends on whenever temporary table can be kept … of scan speed the difference is not so large for data in memory vs data on disk, …
