…MySQL executes this query. So I used commands you’re probably familiar with: EXPLAIN, SHOW PROFILE, SHOW STATUS LIKE ‘Handler%’. EXPLAIN…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
In this post, I’ll cover the new MySQL monitoring plugins we created for Nagios, and explain… in production. The pmp-check-mysql-deadlocks plugin can help you detect such problems, in conjunction with…memory. A runaway server process, or poorly optimized queries, can consume too much memory and cause…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…query was taking almost half the time of time it took it to run in O_DIRECT mode. This can’t be explained…with different read_ahead configuration. Percona Server still allows me to enable Random Read_Ahead which is not available in MySQL…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index_merge” as the type of query and… go mucking about with server configuration, altering the table structure, or creating messy sub-selects. In cases where the optimizer just can’t…) that is the one that MySQL should be looking at, we can rewrite the query like so, with the following result: SELECT user…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…with JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain…LIMIT. However if you’re dealing with LIMIT query with large offset efficiency will suffer…indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query… | 100 | 7 | +———+—–+—+ 1 row in set (0.00 sec) mysql> explain…Optimizer behavior may be different in different MySQL versions. These tests were done with 5.1.45 though I’ve seen same behavior with MySQL…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… be used for optimizing ORDER BY with IN. This original query (in memory data) takes 1sec. Let’s see how classic pre MySQL 5.0 solution… UNION with limit (without order by) optimally – in creates result set for the union fully and when only takes 10 rows from it: mysql> explain…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… changing any queries directly. Unfortunately it does not work. It is interesting to see EXPLAIN for such query and time for the query which… used with very small performance overhead but only in case they are used with caution. MySQL has long way to go getting queries with VIEWs properly optimized.
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with… query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example: mysql> explain…
