… 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…
Comment: Speeding up GROUP BY if you want aproximate results
March 14, 2008 By 24 Comments
[...] the MySQL Performance Blog, Peter Zaitsev advises on speeding up GROUP BY if you want approximate results, which you might if accuracy is expensive and unnecessary when all you want is, “close enough [...]
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…on to any arbitrary degree of parallelism up to, and including, the cardinality of the…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by… sec) Since the data fits in memory speed is near constant and the single …
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
… biggest part yet but if we’d speed up Sphinx and MySQL dramatically we would have … where page_type=’search’ and wtime>1 group by h; +——+———-+—————–+——————+ | h | count(*) | avg(wtime) | sphinx_ratio | +——+———-+—————–+——————+ | 00 | …
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… battery backed up cache would give you only 80-200 sequential fsync() calls per second depending on your hard drive speed. With…
Post: Just do the math!
July 6, 2009 By 4 Comments
… often leads to implementing architectures which are not up for job they are intended to solve. Let … like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY cnt DESC LIMIT 100″ to find the …. This is of course some simplification as processing speed may well be non linear depending on the …
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 index… which is one of the reason covering indexes help to speed up queries even if data is in memory. If MySQL is… 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 user… they will be cached in OS cache or MySQL buffers speeding up further work dramatically.
Post: Feature Idea: Finding columns which query needs to access
November 17, 2006 By 16 Comments
… if you can use covering indexes to speed things up or even cache some data by denormalizing tables. So far it has to be done manually – look at SELECT clause, WHERE clause, ORDER BY GROUP BY and HAVING to sum it up, not to mention more complex questions of subselects…
Page: Presentations
May 3, 2006 By
… also give internal presentations for your company or group. Sphinx search engine for RDBMS Egypt, December … 2007 Tips and Tricks on how you can speed up various things in MySQL as well as … and comparing it to the alternatives. Slides by Andrew Aksenoff, Russian Language. Download MySQL Performance …
