MySQL :- Secondary Indexes and the Clustering Keys

Preface

RDMS Database tables can either be stored as a Heap or Clustered. When stored as a Heap, data is appended as they come in.  When Clustered, data is sequenced based on the Clustering Columns.

Secondary Indexes

For Clustered tables, the Clustering data is written as an additional data on each record.  On the other hand for Heaps, the RID is recorded.

We will soon see that this is an important decision when selecting indexing columns for both Clustered and Secondary indexes.

 

Database

Let us consider the implication of storing the Clustering columns for Secondary Indexes.

MySQL

We will use the same table we used for our last post.

Here is what the table looks like.

Table Columns

TableColumns

Table Indexes

And, here are the Indexes.

TableIndexes

Explanation

We have two indexes:

  1. PRIMARY
    • Columns
      • id
  2. INDX_DBA_RecordCreated
    • Columns
      • record_created

 

Query

Let us issue a query against the table and issue explain to determine if an index is employed and any additional operators included.

SQL

SQL – Fetch on Secondary Index Columns and Clustered Index Columns

SQL Code


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

 

Output

Visual Explain

VisualQueryPlan-20160620-0351AM

SQL – Fetch on Secondary Index Columns, Clustered Index Column, and an additional column

SQL Code


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

 

Output

Visual Explain

VisualQueryPlan

Index Range Scan

VisualQueryPlan_Clipped

Explanation

  1. Secondary Index and Clustering Columns
    • Fully satisfied with index on Secondary Index Columns
      • Operation
        • Index Range Scan
          • Index Name – INDX_DBA_RecordCreated
      • Secondary Index columns consulted for where clause
      • Secondary Index / Clustering Key consulted for projected clustering columns
      • Query Cost :- 1.41
  2. Secondary Index, Clustering Columns, and additional column(s)
    • Though, not all columns that need to be presented are available from the Index, the Index is still very useful for filtering, and it is used
    • Query Cost :- 2.41

 

Summary

Secondary Indexes do not need to include the Clustering Columns, as those columns are automatically hard-wired in.

When all filtering and projected columns are referenced in the Secondary Indexes, they can offer fast and sole access to the needed result.

On the other hand, when a query references other columns besides the columns that make up the Secondary Index and the Clustering Columns, it does not appear that the Access Path is fully realized through the Explain Guide.

But, if we pay close attention to Query Costs we are able to furtherance our comparison.

One thought on “MySQL :- Secondary Indexes and the Clustering Keys

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