MySQL :- Secondary Indexes and the Clustering Keys – Day 2

Preface

In our last post, we spoke about how one might not need to add the Clustering columns when defining a Secondary Index.

At that point, we touched on the fact that just reviewing the Query Plan via “Visual Explain” might not fully reveal whether the Index used is “covering” or whether the Clustered Index is also accessed.

 

Metadata

Indexes

Index – Key name & Columns

List the index names and the corresponding columns

Code


SHOW INDEX FROM dblab.errorLog;

Output

ShowIndex-20160620-1114AM

Explanation

  1. Clustered Index
    • PRIMARY
      • Column Names :- id
      • Cardinality :- 111209
  2. Non Clustered Indexes
    • INDX_DBA_RECORDCREATED
      • Column Names :- record_created
      • Cardinality :- 184

 

Index – Get Index Size

Get Index Size

Code


set @database := 'dblab';
set @table := 'errorlog';
set @convertToMB := 1024 * 1000;

select 
           database_name
         , table_name
         , index_name
         , stat_name
         , @@innodb_page_size as innodb_page_sizeInBytes
         , stat_value*@@innodb_page_size as IndexSizeInBytes
         , (stat_value*@@innodb_page_size) / (@convertToMB) as IndexSizeMB

from mysql.innodb_index_stats 

where stat_name = 'size'

and   database_name = @database

and   table_name = @table

;

Output

IndexStats-20160620-0111PM

Explanation

  1. Clustered Index
    • PRIMARY
      • Size :- 28.2240 MB
  2. Non Clustered Indexes
    • INDX_DBA_RECORDCREATED
      • Size :- 2.5760
  3. Note
    • Note that our table is INNODB, and as such
      • The default page size is 16384 bytes
      • Clustered ( PRIMARY )
        • The data is saved within the Clustered Index

 

Instrumentation

 

Visual Explain

Index Covered

IndexCovered

Index Not Covered

IndexNotCovered

 

Explanation

In the screenshots above, the Visual Explain:

  1. Shows the same exact singular operator for a covered and non-covering Index Scan
  2. The cost is different
    • Covered Index :- 1.41
    • Non Covering Index :- 2.41

 

 

Tabular Explain

Covering Index Exists

Guide

In the Query below, we are filtering on record_created and fetching same column ( record_created ) , and the Clustering Column ( id)

SQL


set @currentTime := Now();
set @currentDate := curdate();
	 
explain select  
            tblEL.record_created
          , tblEL.id
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

 

Output

Explain-Tabular-20160620-1111AM

 

 

Non-Covering Index Exists

Guide

In the Query below, we are filtering on record_created and fetching same column ( record_created ) , the Clustering Column ( id), and an additional column ( userid) which is not part of the Indexed Columns, nor part of the Clustering keys.

SQL


set @currentTime := Now();
set @currentDate := curdate();
	 
explain select  
            tblEL.record_created
          , tblEL.id
          , tblEL.userid
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

Output

Explain-Tabular-20160620-1107AM

 

Explanation

  1. The columns of the Index, INDX_DBA_RECORDCREATED, that we are using is record_created
  2. Here is what Explain returns as a Tabulated Output
  3.  Columns
    • Extra
      • In the first Query, we are covering and the Extra column indicates so by stating ‘Using where; Using index’
      • In the second Query, we are not covering as the Extra column reads ‘Using index condition

Summary

With the Explain command, both the graphical and the tabulated output have merits.

The Graphical has Costing information; while the tabulated furthers our understanding of whether the Index employed fully covers our need or whether additional work needs to be done upon processing the Index.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s