… the response time of the problematic pages: mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg… queries causing the problem you see the ones which cause the most impact and focus on optimizing them. There are multiple ways to optimize…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… we stick to simple MySQL using multiple indexes on most selective columns would be good idea for performance of such queries. For example you… In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates instead of which allows…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache is… slaves and use query cache on all of them cache content will likely be the same, so you have multiple copies of the same…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql… is hundreds of times slower than single row ? If MySQL Optimizer would handle …to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it …); This query runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM() 18 times in the first query… to be taking a pretty significant amount of time; changing to ordinary multiplication made the query faster. (It was not the ultimate suggestion…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… rows/sec. Note multiple drives do not really help a lot as we’re speaking about single thread/query here. So difference… even MySQL optimizer currently does not take it into account. For In memory workload index accesses might be faster even if 50% of… sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution, which…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
…of other queries, not using Full Text Search. [root@r27 sp]# mysql –host 127.0.0.1 –port 3307 Welcome to the MySQL… larger lead in this case because of this optimization but it seems to be broken in … multiple CPU cores and multiple nodes in the system. The raw scan speed was almost 10 millions of…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…optimization from the standpoint of response time measurements, which you should, you will eventually arrive at this question. “I have a query… including the possibility of having multiple key caches….of MySQL performance, and people frequently obsess over it far out of proportion. But again, 99% of…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
… MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer. One way I find IF statement very useful is computing multiple aggregates over different set of rows in the single query…
Post: Wishes for new "Pure PHP" MySQL driver
October 28, 2006 By 17 Comments
…most optimal way to cache things but it would be very easy for many applications. Query…many cases. Multiple concurrent queries I would like to be able to run multiple queries for multiple MySQL connections at …killer to be required to query all of them sequentially instead of doing it in parallel. …
