… it’s not possible to use a patched binary, we can use MySQL Proxy, packet sniffing, or other techniques to get more information than… the query plan. This is where you need to really know how to write queries and how EXPLAIN works. At Percona, we have peer training… (execution times, etc), the EXPLAIN plan, and the desired modifications to the query or the table. We explain how to interpret what we’re showing…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… you suspect EXPLAIN is lieing you you can use SHOW STATUS “Handler” statistics to see if number of operations match. EXPLAIN works for SELECT… table and so how much rows will be stored to temporary table. Sometimes it is enough to perform sorting or use temporary table with… EXPLAIN needs serious overhaul so it can be used with GUI tool to provide tree like structure of opertions applied to the data, methods used…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… of EXPLAIN being wrong – it shows key_len=7 which corresponds to the full key while only first key part is used. Let… – exactly what we tried to avoid. It is easy to block equality propagation by using some trivial function: mysql> explain select sum(events) from… time I figured out how to make MySQL Optimizer to do what we want to do – Just add yet another table to the join so…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…. It is important to look at key_len column in explain plan to see how many index parts are actually used for row lookup. Very… using ORDER BY without and where clauses on the table. In such case you would see “Index” type in explain which correspond to…, B DESC will be able to use full index for sorting (note MySQL may not select to use index for sort if you sort…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to… information in the message. We can see query is using , it is converted to using . And there is some in being done. Unfortnuately… and use IN (1,2,3,…10) it completes in tiny fraction of the second. Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… used. Many people think, that it is faster to use this option than run two separate queries: one – to get a result set, another – to… without LIMIT clause. Let’s check, how long it’d take if we’ll try to use two separate queries: mysql> SELECT SQL… query (2-100 sec). Let’s take a look at EXPLAINs: mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… Optimization is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table… MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT name FROM people… we would like to only lookup people within single zip I would advice to use index in (zip,age) instead of using this workaround…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
… Nagios, and explain their features and intended purpose. I want to add a little context. What problem were we trying to solve with… threshold-based alerts later in this blog post, and explain the need to be careful with them. Although we’re not finished… appropriate, and might need to include others as well. Here’s how you can use our new monitoring plugins to check for some of…
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
…’ve got a complete definition. Now, given the definition above, how do we find these queries in some input such as… can use Amdahl’s Law to decide which of those are candidates for improvement. (The V/M ratio, Apdex score, and the explain sparkline are useful here…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…to use Integers for joins whenever possible and today I worked with client which used character keys, in … Here is explain if someone curious, it did not really change beside key lengths: mysql> explain select sum…it over 2 times slower compared to integer based join. So how do I read these results ? …
