EXPLAIN
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber
FROM accounts_receivable as acc_rec
WHERE acc_rec.cust_id=1;
+----+-------------+---------+------+---------+-------+
| id | select_type | table | type | key | ref |
+----+-------------+---------+------+---------+-------+
| 1 | SIMPLE | acc_rec | ref | cust_id | const |
+----+-------------+---------+------+---------+-------+
EXPLAIN
SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber,
customers.firstname, customers.lastname, customers.phone
FROM accounts_receivable AS acc_rec JOIN customers AS cust
ON acc_rec.cust_id = customers.cust_id;
+----+-------------+---------+--------+---------+-----------------+
| id | select_type | table | type | key | ref |
+----+-------------+---------+--------+---------+-----------------+
| 1 | SIMPLE | acc_rec | ALL | NULL | NULL |
| 1 | SIMPLE | cust | eq_ref | PRIMARY | acc_rec.cust_id |
+----+-------------+---------+--------+---------+-----------------+
While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.
To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:
UPDATE t SET c1 = ‘x’, c2 = ‘y’, c3 = 100 WHERE c1 = ‘x’ AND d = CURDATE()
You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:
EXPLAIN SELECT c1, c2, c3 FROM t WHERE c1 = ‘x’ AND d = CURDATE()
You should now apply the same principles as you would when optimizing SELECT statements.
When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.
The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.
For instance, here is a common query which could be inefficient:
SELECT id FROM t WHERE id='1';
And here is the CREATE TABLE output:
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` decimal(10,0) default NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
You can see it’s a very basic table, and a very basic query.
And looking at the EXPLAIN output, everything still appears normal:
mysql> explain select id from t where id='1'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t | ref | id | id | 6 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
However, now let’s look at the EXPLAIN EXTENDED and SHOW WARNINGS output:
mysql> explain extended select id from t where id='1'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t | ref | id | id | 6 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------+ | Note | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = _latin1'1') | +-------+------+----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
In the WHERE clause of the SHOW WARNINGS output, you see this:
where (`testing`.`t`.`id` = _latin1'1')
The _latin1′1′ is key, as it shows the value of 1 is getting cast as a latin1 value. This is because we surrounded the 1 with single quotes in the query.
Note that this does not happen if you do not surround the 1 with single quotes:
mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------+ | Note | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = 1) | +-------+------+-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
In this example, the constant value of 1 is being cast, which is not horrible, but still not as efficient as it should be. However, depending on how the query is written, I’ve seen where it might choose to cast/convert the row retrived from the database (especially in a JOIN scenario - it will need to convert one column if they differ). These conversions or casts can be very inefficient (especially if within a JOIN or worse, a sub-query), and would not be otherwise caught without using EXPLAIN EXTENDED / SHOW WARNINGS.
And in addition to the above example, it is common to see similar types of conversions occurring if you try to match two columns whose character sets differ. This can be another easy-to-overlook problem, which can lead to poor performance, and you wouldn’t know it wasn’t running efficiently unless you used EXPLAIN EXTENDED (or unless it brings your system to a halt during a heavy load ..).
So in conclusion, consider using EXPLAIN EXTENDED / SHOW WARNINGS in lieu of just EXPLAIN when analyzing your queries.
Note: If you are using pre-5.1.46, you may want to consider running both EXPLAIN and EXPLAIN EXTENDED to ensure the optimization plans match. This is due to the following bug which existed:
http://bugs.mysql.com/bug.php?id=47669
Today at Kaleidoscope I will be doing a 90-minute session comparing MySQL’s SQL syntax to the ANSI/ISO SQL:2003 standard, entitled What Do You Mean, “SQL Syntax Error”?
You can download the PDF slides now.
For those that may be following along the presentation later today (4 pm Eastern time), here are some links that I may throw out during the session:
This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today.
MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session.
Here's the tentative list of upcoming sessions:
Here’s a quick tip for using explain:
You may know this already, but mysql will actually execute some subqueries when you invoke explain. Here’s an example:
mysql> explain select id from projects where id = (select max(id) from projects where name like 'en%'); +----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+ | 1 | PRIMARY | projects | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 2 | SUBQUERY | projects | ALL | NULL | NULL | NULL | NULL | 67922 | Using where | +----+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------+ 2 rows in set (0.11 sec)
Take a look at the execution time (I choose an intentionally poorly executing query for my little dataset).
Here’s explain when it’s not executing:
mysql> explain select max(id) from projects where name like 'en%'; +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | projects | ALL | NULL | NULL | NULL | NULL | 69513 | Using where | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)
If you want to work around this to prevent trouble on a production server (albeit, not getting the output from explain), you can do this:
mysql> set session max_join_size=1; Query OK, 0 rows affected (0.00 sec) mysql> explain select id from projects where id = (select max(id) from projects where name like 'en%'); ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
Related posts:
Related posts brought to you by Yet Another Related Posts Plugin.
I've seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed. This method is flawed - here's the first reason why:
The cardinality of status index is woeful, but provided that the application is always only sending query 1 to MySQL it's actually a pretty good index! It's not always like this, but there are a lot of cases where applications have good selectivity with some queries despite what cardinality shows.
Not convinced? Here's reason number two:
The index on query 3 had high cardinality but should not be used since too many countries have a population greater than 1000. An automated search for low cardinality indexes wouldn't have revealed it's uselessness. For range scans, it's very easy to lead yourself into a trap where your index can not filter out enough rows to be effective. I see this a lot in consulting issues where customers have queries that use a BETWEEN on a date, but the window of time it is searching in is too wide.
Side Note: In some texts you'll see people quote the numbers "20-30%" as the minimum amount of rows you have to filter down to for an index to be useful (that is, eliminate 70-80% of rows). It's not quite correct to quote this as an exact percentage, since this value is not fixed in MySQL and can be a much wider window (15-60%) depending on the circumstances. In this case, MySQL flipped from tablescan to index at about 34%.
How am I supposed to find unused indexes then?
You really have to run queries against your server - there is no other way. From there, there's a helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not.
If you are not running a patched server, then the alternative is to either use a proxy that checks EXPLAIN information (like QUAN) or set your slow query log to zero microseconds (5.1 feature) and then find someway to parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There's an old tool called mysqlidxchx which should be able to do this.
Entry posted by Morgan Tocker | No comment
Add to: 
| 
| 
| 
| 