…, I believe it should all translate to the same execution plan. (I know it doesn’t though.) Why don’t you… (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first, by looking up the keywords… MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…, I plan to focus on a series of posts on the small but handy improvements – changes and bug corrections – in MySQL 5… warning (0.56 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1831 Message: Duplicate… 2005!). The report explains more in detail what the “will be disallowed in a future release” means. In MySQL 5.7 the…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement, …rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; …
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
… compatibility between version and so you can run MySQL 5.0 database with MySQL 5.1 with no problems in most cases…, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL generally supports legacy storage format for a while, even… run SELECT queries on both new and old MySQL installations and check result set, explain plan and execution time, reporting you on all…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… 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… test the plan: mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; mysql> explain extended…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult… examine the query plan. This is where you need to really know how to write queries and how EXPLAIN works. At Percona… (execution times, etc), the EXPLAIN plan, and the desired modifications to the query or the table. We explain how to interpret what we…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… between MySQL Server versions you should: Check Query Plan Run EXPLAIN to see if plans for the query are the same. Changing Query Execution plans is the most common regression problem. If plan…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… the EXPLAIN plan for a given SELECT would show N possible index choices but only one index actually used. To some extent, MySQL…. It’s fairly straightforward to tell when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5… MySQL 5.5 which can produce very expensive plan selections ? Investigating this further I found the following explanation from Timour Katchaounov in MySQL… in join I think faster execution plan computation would be more important than backward compatibility. In MySQL 5.6 things are likely…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…/4th of a second. If you look at the abridged explain plan below, you’ll see the query is doing a table… first query, and then executing the subquery for each row: mysql> explain select ….\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table…
