
###################### 
# EXPLAIN command
#  shows the execution plan of a query
 

source ../../examples/library/mylibrary.sql;

######################
# select .... from A where ...
######################

select titleID, title from titles where year = 2003;

explain select titleID, title from titles where year = 2003;



######################
# list of tables in the order in which tables are read 
# how join is executed with previous table and used indexes
#
# id: number assigned to query and subqueries 
# select_type: SIMPLE, PRIMARY (outmost select)  
#              UNION (result of a union), 
#              SUBQUERY (first select in a subquery),
#              DERIVED (subquery in a from clause), 
#               ........
#  
# table: table to which the row of output refers
# type: describe how the table is related to other tables
# possible keys: possible indexes to choose
# key: index actually chosen
# key_len: len of the chosen key
# ref: which columns or constants are compared to the index named #      in the key column to select rows from the table
# rows: estimate of rows to be examined
# extra: additional information on how the query is solved



######################
# simple examples 

describe titles;

alter table titles add  index index_year(year);

describe titles;

explain select titleID, title from titles where year = 2003;

# if the index covers the selected column, index scan more 
# efficient than  file scan 
explain select year from titles where year = 2003;
explain select count(*) from titles where year = 2003;

explain select* from titles where title LIKE '%rms';

explain select* from titles where title LIKE 'Wind%';

explain select title, year from titles where year !=2003;

explain select title, year from titles where isnull(year);

######################
# JOIN TYPE (ordered from the best type  to the worst type) 
# 
# system: the table has only one row (special case of const type)
#
# const:  the table has at most one matching row,
#         values from the columns of this row can be regarded as 
#         constants by the rest of the optimizer 
#         For example when we compare a PRIMARY KEY or UNIQUE   
#            INDEX to a constant value 
#          select * from ref_t
#                where primaryKey_column=1
#
# eq_ref:  For example, when PRIMARY KEY or UNIQUE INDEX  
#          is used by the join  
#          select * from ref_t, t1 
#                where ref_t.Key_column=t1.column
#          one row is read from ref_t (for each 
#          combination of rows from the previous tables)
#
# ref:  if the join cannot select a single row based on the key    
#       value  (NOT PRIMARY KEY and not  UNIQUE INDEX)
#       using = or <=>
#       select * from ref_t where ref_t.Key_column = 6
#       select * from ref_t, t1 where ref_t.Key_column=t1.column
#               
#       all rows with matching index value are read 
#       from ref_t (for each combination of rows from 
#       the previous tables)
#
# fulltext: a FULLTEXT index is used to solve the join
#
# ref or null: like ref but extra search for rows that contain 
#              NULL
#
# range: key column compared to a constant using =, >=, <=, ...
#        isnull, between, ...
#        an index is used to select the rows
#          select * from ref_t
#                where Key_column=10
#          select * from ref_t
#                where Key_column between 10 and 20
#
#
# index: full index scan. The same as ALL, except that the 
#        index is scanned. If the index is a covering index          
#        for the query, only the index tree is scanned 
#        (size of the index smaller than size of the table)
#        Extra column says Using index. 
#        full able scan using the reads from the index to 
#        look up data row in index order.      
#              
#   ....... subqueries .....            
#               
# all: a full table scan is done for each combination of 
#      rows from the previous table 



######################
# indexes on columns are used if they are declared the same 
# type and size: VARCHAR() and CHAR() must have the same size
######################


####################### cartesain product 
explain select * from titles, rel_title_author, authors;

####################### index on authors not used 
explain select * 
  from titles, rel_title_author, authors 
    where rel_title_author.authID=authors.authID and   
             rel_title_author.titleID=titles.titleID;

####################### index on authors is used 
explain select titles.title, authors.authname 
    from titles, rel_title_author, authors 
       where rel_title_author.authID=authors.authID and   
                rel_title_author.titleID=titles.titleID;

####################### natural join
explain select * 
 from titles natural join rel_title_author natural join authors;


###################### DERIVED SUBQUERY
explain select title from (select * from titles where year = 2003) as A;

drop index index_year on titles;

explain select title from (select * from titles where year = 2003) as A;

###################### ORDER BY
explain select title, authname 
    from titles, rel_title_author, authors 
       where titles.publID=2 and
              rel_title_author.authID=authors.authID and   
                rel_title_author.titleID=titles.titleID
     order by title;

###################### DEPENDENT SUBQUERY
explain select title from titles where publID in 
(select publID from publishers where  publID=16);

