Using EXPLAIN EXTENDED to see view query rewrites

At the MySQL Mini Conference in Sydney this week we discussed how to use EXPLAIN EXTENDED to view the rewrites undertaken by the MySQL optimizer.  IN particular, to see if MySQL performs a merge of the query into the view definition, or if it creates a temporary table.

It can be tricky to optimize queries using views, since it's often hard to know exactly how the query will be resovled - will MySQL push merge the text of the query and the view, or will it use a temporary table containing the views result set and then apply the query clauses to that?

In general, MySQL merges query text except when the view definition includes a GROUP BY or UNION.  But to be sure we can use EXPLAIN EXTENDED.  This also helps when we get confusing output in the EXPLAIN output.

For instance if we have a view definition like this:

 

CREATE VIEW user_table_v AS
     SELECT *
       FROM information_schema.tables ist
        WHERE table_type='BASE TABLE';

and try and explain a query like this:

 

explain select * from user_table_v WHERE table_schema='mysql'\G

We get output like this, which might be difficult to interpret unless we know the view defition:

 

*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: ist
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where

Note the table "ist",  only by looking at the view definition can we interepret this.  But if we do an EXPLAIN EXTENDED followed by a SHOW WARNINGS we see the exact text:

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `ist`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`tables` `ist` where ((
`ist`.`TABLE_SCHEMA` = _utf8'mysql') and (`ist`.`TABLE_TYPE` = _utf8'BASE TABLE'))
1 row in set (0.00 sec)

And from this we can see that MySQL did indeed merge the WHERE clauses of both the query and the view definition.

If we look at the output for a view like this:

CREATE VIEW table_types_v AS
       SELECT table_type,count(*)
         FROM information_schema.tables ist
        GROUP BY table_type;

The we see the following output, in which we can see that MySQL created a temporary table and then applied the WHERE clause from the query:

*************************** 1. row ***************************
          id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE noticed after reading const tables
*************************** 2. row ***************************
          id: 2
  select_type: DERIVED
        table: ist
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using temporary; Using filesort
2 rows in set, 1 warning (0.00 sec)

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `table_types_v`.`table_type` AS `table_type`,`table_types_v`.`count(*)` AS `count(*)
` from `mysql`.`table_types_v` where (`table_types_v`.`table_type` = _utf8'BASE TABLE')
1 row in set (0.00 sec)

EXPLAIN EXTENDED is an invaluable tool for tuning SQL statements, and even more so when working with views.

Comments

[p]

Still far from a proper explain plan like the ones of Oracle or PostgreSQL ...

[Idetrorce]

very interesting, but I don't agree with you
Idetrorce

[air jordans]

It is not true suffering ennobles the character; happiness does that sometimes, but suffering, for the most part, makes men petty and vindictive. Do you understand?

[Aceffippertut]

Hi everyone

http://www.internetmoque.net - Translations of the meaning of the Quran

Islam is `the religion of peace' because:

the Arabic word Islam is derived from the Arabic word "Al-Salaam" which means peace.

It might seem strange to think of this as a misconception, but in fact it is. The root word of Islam is "al-silm" which means "submission" or "surrender." It is understood to mean "submission to Allah." In spite of whatever noble intention has caused many a Muslim to claim that Islam is derived primarily from peace, this is not true. Allah says in the Qur'an (translated):

[2:136 - Say (O Muslims): We believe in Allah and that which is revealed to us and that which was revealed to Abraham, and Ishmael, and Isaac, and Jacob, and the tribes, and that which Moses and Jesus received, and that which the prophets received from their Lord. We make no distinction between any of them, and to Him we have surrendered. [Arabic "Muslimoon" -

A secondary root of Islam may be "Al-Salaam" (peace), however the text of the Qur'an makes it clear that Allah has clearly intended the focus of this way of life to be submission to Him. This entails submission to Him at all times, in times of peace, war, ease, or difficulty.

For more details http://www.internetmoque.net - click her

[scoma]

you definitely love italian designer at my estore bag italian to your friends

[scoma]

sell ugg boots sale uk for gift to your friends

[online pharmacy india]

online pharmacy prices Buy Cheap Fast, Free pills added! levofloxacin, other antibiotics or medicines, foods, dyes, or preservatives Metaglip cheapest place to Hong Kong FDA Approved recently exposure to alcohol or any product that contains alcohol online, Without prescription.Bronchitis Antibiotics depressed, thoughts of suicide Metaglip cheap+non prescription Nevada without perscription small amount of semen released during sex other antidepressants other than.Buy cheapest brand USA without prescription levothyroxine, other thyroid hormones, other medicines, foods, dyes, or preservatives Lasix Info about Alcohol and Las Vegas cod no prescription +FREE Pills

for sale comprar lamictal dispersible, low price comprar lamictal, without prescription comprar lamictal,

[online pharmacy worldwide shipping]

online pharmacy no prescription canada Buy Where to buy o order online brain damage Diovan hct internet sexual difficulties (less sexual desire or ability to get an erection) To Get online without prescription.Buy Where to buy o order online sulfamethoxazole, trimethoprim, sulfa drugs, other medicines, foods, dyes, or preservatives Diovan hct cheapest online price Glasgow FDA Approved spasm in fingers or toes, or unusual muscle movements Who knows where to cheapest United States Discount Drug Shop.Antibiotics cheap buy amantadine, rimantadine, other medicines, foods, dyes, or preservatives Cozaar cheapest Suomi health-care Next Day delivery

buy generic vasotec buy generic vasotec buy generic veetids buy generic veetids buy generic ventolin buy generic ventolin

[buy Plavix]

Thanks for sharing your ideas here. The other matter is that if a problem appears with a personal computer motherboard, individuals should not have some risk regarding repairing that themselves because if it is not done correctly it can lead to irreparable damage to all the laptop. In most cases, it is safe to approach the dealer of any laptop for any repair of the motherboard. They have got technicians with an experience in dealing with pc motherboard challenges and can make the right prognosis and carry out repairs.

cheap Priligy