… is it so expensive? I typically see queries like this: select …. from … order by …. limit X, 20 If the ORDER BY can…-organizing data, or query optimizations, there are two big things you can do. One is caching aggressively to prevent these queries from running. The… are four suggestions for optimizing paginated displays that can give significantly better performance. On the first query, fetch and cache all the results…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We… same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…First lets talk about optimizing application vs getting better hardware. With application optimization typically you have …than throughput. If single user CPU bound query takes 30 seconds you unlikely will … limited by MySQL server so the alternative would be either spend $5000 on implementing caching…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache…limit 10) ORDER BY last_online desc limit 10; …
Post: Caching could be the last thing you want to do
July 24, 2010 By 14 Comments
…have exhausted all other easily reachable optimizations. What other optimizations are possible? Before implementing caching, here is a non-exhaustive …data, and (potentially) limiting further optimizations like covering indexes. Do you have information about how many queries were required to generate…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache…queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and optimization…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… hard to prove, there actually is a very limited use for the statistics from which the ratio …the query‘s execution time. Alas, that correlation fools us into thinking it’s a cause, and we optimize-… expensive than hits, then you care about cache misses, not cache hits. A Key_read is not guaranteed …
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…Cache The fact Falcon has separate row cache is great as it means it can have much more data cached in the same cache…having index covered queries is great optimization for wide range of queries. [+] Ordered data … transaction. [-] Isolation Modes So far limited amount of isolation modes is supported…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… limitations in the fast index creation feature introduced … | | end | 0.000007 | | Waiting for query cache lock | 0.000002 | | end | 0.000007 | | query end | 0.000003 | | closing tables… 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE ……
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…) – 1 As with any performance optimization effort, you will want to optimize for the common case. If you…the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these…_max_dirty_pages_pct=90 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache…
