… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local memory, which is great however memory amount might be limited especially in… compress results at faster than wire speed for 1Gb networks. Plus if we can get MySQL protocol to support such light compression…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query…query wiping your all query cache option query_cache_limit was implemented which limits result set which can be stored in query…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… what they are, both in terms of size and spindle speed. I need to know how many physical spindles are under… most expensive queries, in total execution time. By the way, the slow query logs in the stock MySQL server are extremely limited, and for… in the slow query log output. The stock MySQL server’s limitation of one-second granularity makes it hide problem queries that are faster…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… MySQL is not smart enough in this case to “dive into” the union and add ORDER BY ORD LIMIT 10 to individual queries. What if we do int manually ? mysql> explain (select * from utest where c1=5 order by ord desc limit… is used for finding rows or sorting as well) but query speed goes to less than 0.01 sec. So it is…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… considerably. In similar way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset values very expensive. To get better performance you can “Help” MySQL…
Post: Figuring out what limits MySQL Replication
March 7, 2007 By Leave a Comment
… could be the limiting factor for this system quite soon, so I decided to check what is limiting MySQL Replication speed. My first idea was to check it based on slow query log, happily the server was running MySQL with slow query log with microsecond resolution so I could check exactly which update queries…
Post: Upgrading MySQL
January 5, 2010 By 23 Comments
… slower checking your progress along the way. There is no limit as Safe you can get so I would just outline… just for this project. Query Validation It is often hard to ensure queries work same way with different MySQL version, unless you have… single stream of queries with good speed is not enough. You also need to perform stress testing to ensure both MySQL does not…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… speed: mysql> select * from gt order by i desc limit 10; +——–+——————————————+ | i | c | +——–+——————————————+ …mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order by i desc limit…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes… lot of this mistakes come from missing one very important MySQL limitation – once MySQL runs into the interval range it will not use… the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not…
