… sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv; Unfortunately this query ran for… sum(cnt) from (select count(*) cnt from performance_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031 | +———-+ 1…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by… set (5.00 sec) Since the data fits in memory speed is near constant and the single threaded operation burns one…(word))`,COUNT(*) AS `count(*)` FROM words2 AS `words2` GROUP BY 1,2,3 ORDER BY NULL ) The other important optimization combines results from…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… have not asked it. Another thing to try is GROUP BY – Sphinx executes GROUP BY in fixed memory which means results may be approximate…_id) as m,author_id as a from sptest group by author_id order by m desc limit 10; +————+——–+———-+———–+———+ | id | weight | forum_id… cores and multiple nodes in the system. The raw scan speed was almost 10 millions of rows per second (this is…
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
… 10% of response time in average even speeding it up 10 times we would not …081221 where page_type=’search’ and wtime>1 group by h; +——+———-+—————–+——————+ | h | count(*) | avg(wtime) | sphinx_ratio | +——+———-+—————–+——————+ | 00 | …
Post: Just do the math!
July 6, 2009 By 4 Comments
… have query like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY cnt DESC LIMIT 100″ to find the most visited… 100MB/sec scan speed we can read 200K rows/sec. Note this number can be affected a lot by file fragmentation, on… memory too. This is of course some simplification as processing speed may well be non linear depending on the data size…
Post: Group commit and real fsync
May 3, 2006 By 9 Comments
….0 which did not work with former group commit code. The new code for group commit however was never implemented. XA allows… this issue is trigered by enabled binary log. if binary log is disabled, so is XA and old group commit code works… sequential fsync() calls per second depending on your hard drive speed. With fake fsync() call the data is only written to…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… the two databases, including: Ease of installation Loading capability and speed of loading Accuracy of results of queries over the large…. ICE supports almost all of the MySQL aggregation functions. Notably, GROUP_CONCAT is not supported, which is something I hope they… that was higher than it should have been, and several GROUP BY queries returned unexpected results as well. I did not change…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… the same table as I used for MySQL Group by Performance Tests to see how much MySQL can …is 32K which gives us the following speed: mysql> select * from gt order by i desc limit 10; +——–+——————————————+ | i | c | +——–+——————————————+ …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… first place. Most popular index type in MySQL – BTREE can speed up equality and prefix range matches. So if you have… which is one of the reason covering indexes help to speed up queries even if data is in memory. If MySQL… indexes. You can also see others like using index for group by but I think they can be pretty much looked as…
Comment: Why MySQL could be slow with large tables ?
June 19, 2006 By 139 Comments
… example if you do GROUP BY and number of resulting rows is large you might get pretty poor speed because temporary table is… more managable you would get your data clustered by message owner, which will speed up opertions a lot. Also this means once… they will be cached in OS cache or MySQL buffers speeding up further work dramatically.
