…_size=1000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (0.70 sec) mysql> show…_size=10000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (1.34 sec) mysql> show… do 1.34sec ! Lets try it even higher to finally get rid of sort merge passes – may be it is sort merge…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values on…%” group by grp with rollup) t order by cnt desc limit 11; +——+———+ | grp | cnt | +——+———+ | NULL | 2352996 | | 9879 | 300 | | 3888 | 298 | | 1793… table for buffering helps us to get result set we’re looking for and workaround this limitation, though it of course comes…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… them would so it gets rid of all of them. This is one of the main features which limits query cache effectiveness – if… large query caches, which might be unsuitable for online applications. Limited amount of usable memory – Queries are constantly being invalidated from… query wiping your all query cache option query_cache_limit was implemented which limits result set which can be stored in query…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… lot more bugs and annoyances. There are already inconvenient limitations on some key parts of partitioning, such as maintenance and… you don’t need data anymore, move it away or get rid of it. I wrote a three-part article on… can be better than the alternative, if the alternatives are limited, but they’re still not what you need unless you…
Post: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
…’s developers may not agree with that ) with some serious limitations (like using mysqldump for node propagation). The Galera team heard… promising. Well, it took 1.5 years to fix the limitations and come up with new features, but there is nothing… both masters and do not worry about conflict resolution and get rid of those “slave is out of sync with master” pain…
Post: Slow Query Log analyzes tools
September 6, 2006 By 83 Comments
… candidates for optimization but it has few serious problems which limits extent to which it is helpful. First – it only allows… query running once per second. We of course want to get rid of really slow queries but to really optimize application throughput…_query_time without restarting server so it is easy to get sample of all queries for some period of time and…
Post: Speedup your LAMP stack with LigHTTPD
May 21, 2006 By 14 Comments
…. There are multiple solutions and workarounds to this problem: Just get more memory/more boxes. The good thing with Web servers… your choice you can stop reading 
Restrict resources. You can limit number of Apache children (MaxClients 50) , disable keep alive (KeepAlive… solution more – it allows you to get rid of all the fat Apache server has and get efficient event driven socket processing. This…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… be very inefficient. As results of Foreign Key limitations you might need to get rid of them, yet this leaves you up for a final bite – dropping foreign keys requires table rebuild. Yes you get…” column in child table, even to same value you will get foreign key violation error. So Innodb Foreign Keys are pretty…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… where conflicts will be frequent. And now even laptops are getting multi cores so we can forget about spinlocks wasting CPU… functionality is not exported well as it requires you to get rid of primary keys etc. Some presentations say Falcon does not… modified/inserted after you started transaction. [-] Isolation Modes So far limited amount of isolation modes is supported, including no support for…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… of copying and conversions while accessing the data, which significantly limits performance compared to basically “zero copy” design one may have… LVM backup may not work in all cases. We also get into synchronization aspects making backup which prevents from having hot… could tune Optimizer to handle Innodb specifics well. We could get rid of SQL level table locks and using Innodb internal data…
