3

I have a simple SELECT * From tv Where Client = 'ABCD' query and when I do an EXPLAIN EXTENDED, it gives me two different results. When executing the query, one of them take a few milliseconds, while the other takes about 3 seconds. Why would it give two different explain results and what is causing the slowness?

Slow Query:

Slow Query

Fast Query:

Fast Query

Xaisoft
  • 45,655
  • 87
  • 279
  • 432
  • 4
    You need to share with us the two **exact** queries – gvee Aug 13 '13 at 19:59
  • @gvee - It is in the POST. `SELECT * FROM TableA Where Client = "ABCD"`. The difference between the two is that one is run in an in-house server and the slow query is run at a colo. – Xaisoft Aug 13 '13 at 20:01
  • 1
    Well your images show a table called "tv", not "TableA". Also, the double quotes should be single quotes. Hence the confusion. What's a "colo"? – gvee Aug 13 '13 at 20:03
  • @gvee - I will change it. Colo is colocation. The server resides at another remote location where one resides here in-house – Xaisoft Aug 13 '13 at 20:04
  • Well, based on the information in the screenshots I would note that the `key_len` is different. Have you confirmed that the table definitions match exactly? – gvee Aug 13 '13 at 20:06
  • @gvee -The only difference I see is the AVG_ROW_LENGTH is 484 for the slow query and 473 for the faster one. Everything else, indexes, fields, size of fields, primary key is the same. – Xaisoft Aug 13 '13 at 20:10
  • I don't understand why one shows a key_len of 32 and the other 55. They both have the same primary key and index and the fields are the same length and type. – Xaisoft Aug 13 '13 at 20:16
  • 1
    Could you connect to both of your servers and show us the result of `SHOW CREATE TABLE tv`. Please *edit your question* to add that. It is rather inefficient to have to dig through comments to find the various pieces of information. – Sylvain Leroux Aug 13 '13 at 21:36
  • Are you sure these two tables are using the same storage engine? Is one InnoDB and the other MyISAM? Is it the same version of MySQL running on both servers? The "fast query" is showing a "Using filesort", and there's no indication in the query that the rows in the resultset need to be returned in any particular sequence, so that's odd. If this is a composite primary key, then the order of the columns may be different. – spencer7593 Aug 13 '13 at 22:24
  • 1
    @spencer7593 `SHOW CREATE TABLE tv` would display most of those information. Without that we could only speculate... – Sylvain Leroux Aug 13 '13 at 22:56
  • ... for example, we could say that the optimizer use outdated information to build the query execution plan, and that we should probably need to use [`ANALYSE TABLE tv`](http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html) to update those information. But that would be most speculative ;) – Sylvain Leroux Aug 13 '13 at 23:00

5 Answers5

9

Q Why does the same exact query produce 2 different MySQL explain results?

A Because something is different. If not in the query, then between the two tables, or database instances.

All of these should be reviewed, to find the difference:

  • Are they running on the same version of MySQL (SHOW VARIABLES LIKE '%version%')
  • Are the instances running the same characterset (SHOW [GLOBAL] VARIABLES LIKE 'character_set%')
  • Are the table columns using the same characterset (SHOW CREATE TABLE)
  • Are both tables using the same storage engine? (SHOW CREATE TABLE)
  • If the primary key is a composite key, are the columns in the same order (SHOW CREATE TABLE)
  • Are statistics up to date and accurate?
  • Is one of the tables fragmented, due to a lot of insert,update,delete activity?
  • Is the MyISAM key cache or the InnoDB buffers the same size on both servers?
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Everything on the table is the same except the fields in the index are not in the correct order. Two of the fields are flipped. – Xaisoft Aug 14 '13 at 13:47
  • Is there a command I can run to see if the statistics are up-to-date and accurate? – Xaisoft Aug 14 '13 at 13:47
  • Do you think the **order of the columns** in the index might have something to do with the **execution plan** of a query? Do you think that the indexes that are defined with columns in a **different order** would be considered equivalent or different by the optimizer? I have an idea... why not try creating an index that is defined the **SAME**, and see if that has any influence. – spencer7593 Aug 14 '13 at 14:21
6

I solved by updating table statistics.

On MySQL i did:

OPTIMIZE TABLE [tablename]
Pedro Casado
  • 1,705
  • 1
  • 21
  • 43
1

Well the estimated number of row are also different.

So MySQL uses table statistics to determine which indexes to us and how to use them. Since the tables appears to have a different amount of rows in it it is only reasonable that the query plans would differ as the statistics will be different.

Update:

I did not read the row column correctly. Thus I assumed there is a huge difference in rows. This is not the case. Seems like the statistics might be out of date on the table with the slow query. Please run a OPTIMIZE TABLE statement on the slow query table. This will essentially rebuild the table.

Namphibian
  • 12,046
  • 7
  • 46
  • 76
0

the slow query shows the type as ref while the fast query shows the type as range. I suspect that you are missing an index on your Client row on the slow table.

Claies
  • 22,124
  • 4
  • 53
  • 77
-1

I can confirm this kind of behavior. Just spent whole day to get it. Sometimes, when you are not using statements PRIMARY = PRIMARY (eg. using just part of the composite primary key), Mysql (resp. MariaDB) is executing MUCH faster queries on DB with a lot of data (production DB), instead of DB with just sample data (production env.)

My solution was to copy part of prod. data to dev database - it made some queries executed with different strategy, and of course, faster.

Ivan
  • 315
  • 1
  • 3
  • 16