DBeaver – Table Relationships

Background

Just wanting to make sure that I am properly tracking table relationships in MySQL.

 

Code

INFORMATION_SCHEMA

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Code

Sample


set @schema := 'sakila';
set @table := 'inventory';

select 

         case

            when tblKCU.TABLE_NAME = @table then 'References'
            when REFERENCED_TABLE_NAME = @table then 'Referenced'

         end as 'isReferencingOrReferenced'
       , tblKCU.CONSTRAINT_NAME as 'constraint'
       , tblKCU.TABLE_NAME as 'table'
       , tblKCU.COLUMN_NAME as 'column'

       , tblKCU.REFERENCED_TABLE_NAME as 'refTable'
       , tblKCU.REFERENCED_COLUMN_NAME as 'refColumn'

       , tblKCU.ORDINAL_POSITION as 'colPos'

from   information_schema.KEY_COLUMN_USAGE tblKCU

where  tblKCU.TABLE_SCHEMA = @schema

/* Foreign Key Relationship */
and    (

             ( tblKCU.TABLE_NAME = @table )
          or ( tblKCU.REFERENCED_TABLE_NAME = @table )

       )

/* Skip Primary Key */
and    (

              ( tblKCU.TABLE_NAME is not null )
          and ( tblKCU.REFERENCED_TABLE_NAME is not null )

       )

order by       

         tblKCU.TABLE_NAME
       , tblKCU.COLUMN_NAME

       , tblKCU.REFERENCED_TABLE_NAME
       , tblKCU.REFERENCED_COLUMN_NAME

       , tblKCU.CONSTRAINT_NAME       

       , tblKCU.ORDINAL_POSITION

;

Output

 

Tools

DBeaver

Outline

Steps to follow to track visually through DBeaver

  1. Launch DBeaver
  2. Connect to MySQL Instance
  3. Navigate to Database Tables <Specific Table>
  4. Double Click on the selected table
  5. On the right panel, choose the “ER Diagram” Tab
  6. Review the shown tables and their relationships to each other

Image

MySQL – information_schema.statistics

Background

As a quick follow-up to our discussion of metadata on tables, let us discuss indexes.

BTW, the discussion on tables is here.

INFORMATION_SCHEMA.STATISTICS

Code


select

		tblStat.TABLE_NAME as 'table'

	  , tblStat.INDEX_NAME as 'index'

	  , case tblStat.NON_UNIQUE
			   when 0 then 'No'
			   when 1 then 'Yes'
			   else '?'
		end as 'Unique'


	  , GROUP_CONCAT( COLUMN_NAME order by SEQ_IN_INDEX ) as colList


	  , tblStat.CARDINALITY


from  INFORMATION_SCHEMA.STATISTICS tblStat


where  tblStat.TABLE_SCHEMA = 'drupal'


group by

		 tblStat.TABLE_SCHEMA
	   , tblStat.TABLE_NAME
	   , tblStat.INDEX_NAME


order by

		 tblStat.TABLE_SCHEMA
	  ,  tblStat.TABLE_NAME
	  ,  tblStat.INDEX_NAME


LIMIT 25

;


Output

 

Comparison with Other Databases

  1. GROUP_CONCAT
    • The group_concat is very, very powerful and has few equal in other database platforms
    • Function Documentation

 

SHOW INDEX

To get a bit more information, specifically cardinality ( Number of unique values in each column combination ), we can issue “show index” against the table.

Code

Syntax


show index from [table] from [database];

Sample


show index from search_index from drupal;

Output

References

  1. Reference
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA STATISTICS Table
    • Show Index
    •  Group_Function
      • Group By Function
        Link
    • Group_Concat

DBeaver

Background

Here I am having used SQL Server Management Studio ( SSMS ) for so long, I am satisfied.

It’s GUI is versely superior to SQL 2000 Query Analyzer and it exposes just about every SQL Functionality that I use on daily basis.

 

Stumped

Spent hours trying to capture a query’s grid display unto Excel, but Excel column size was getting in the way.

And, so googled for ways to display columns as rows.

 

DBeaver

Googled and found DBeaver.

The nice thing about it is that I can export queries result set into XML or HTML.

 

Download

DBeaver is available here.

 

Export Query

Screen Shot

Data Transfer Target Type and Format

Image

Data Transfer – Extraction Settings

Image

Data Transfer – Settings – See export settings

Image

 

Data Transfer – Output – Configure export output parameters

Image

Data Transfer – Confirm – Check results

Image

 

Sample Files

HTML

XML

 

Other Functionalities

Database Diagram

Database Modeling is also easy and nice.

 

Table Structure

Properties

References

DDL

 

Data

 

Database Supported

DBeaver supports both SQL and NoSQL Databases.

For SQL databases, it relies on JDBC.

Driver Manager

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.

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.

Transact SQL – Constraint – Primary Key

There are a couple of approaches one can use to get the primary key for a table.

Here are some of those ways:

  • sp_pkeys
  • sp_primarykeys
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS

sp_pkeys

Syntax:

exec sp_pkeys
@table_name = [table-name]
, @table_owner = [schema-name]
go

Sample:

exec sp_pkeys
@table_name = 'DimEmployee'
, @table_owner = 'dbo'
go

Output:

sp_pkeys

sp_primarykeys

Though sp_primarykeys was added to gain insight into remote data sources, you can use it it to query your local data source, as well.

Btw, to get foreign key data on remote data sources, please refer to sp_foreignkeys (Transact-SQL) – http://technet.microsoft.com/en-us/library/ms187337.aspx.

Syntax:

exec sp_primarykeys
table_server = [SQLInstanceName]
, table_catalog = [DatabaseName]
, @table_name = [tableName]
, @table_owner = [schemaName]
go

Sample:


use [AdventureWorksDW2008R2]
go

declare @serverName sysname
declare @databaseName sysname

--set server name to current SQL Instance
set @serverName = cast(SERVERPROPERTY('servername') as sysname)

--set database name to current database
set @databaseName = DB_NAME()

exec sp_primarykeys
@table_server = @servername
, @table_catalog = @databaseName
, @table_name = 'DimEmployee'
, @table_schema = 'dbo'
go

Output:

You might likely get a subtle error:


Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
Server server-name is not configured for DATA ACCESS.

And, to correct try:

  • Enabling Data Access on the Data Source
  • The Data Source in this case is any registered OLE-DB Provider and so you can query the local or a remote data source

declare @serverName sysname
declare @isDataAccessEnabled bit

--set servername to local server
set @serverName = cast(SERVERPROPERTY('servername') as sysname)

--get data access enabled flag
select
@isDataAccessEnabled = tblServer.is_data_access_enabled
from sys.servers tblServer
where tblServer.name = @serverName

print '@isDataAccessEnabled :' + cast(@isDataAccessEnabled as sysname)

--if data access to sql server instance is disabled, please enable
if (@isDataAccessEnabled = 0)
begin

print 'Data Access on ' + @serverName + ' is currently disabled'

print 'Allowing Data access ' + @serverName + ' ...'

exec sp_serveroption @serverName , 'data access', 'true'

print 'Data Access on ' + @serverName + ' is now enabled'

end

 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Syntax:


select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblConstraint
where tblConstraint.CONSTRAINT_TYPE = 'PRIMARY KEY'
and     tblConstraint.CONSTRAINT_CATALOG = [database-Name]
and     tblConstraint.TABLE_SCHEMA = [schema-name]
and     tblConstraint.TABLE_NAME = [table-name]
go

Sample:


select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblConstraint
where tblConstraint.CONSTRAINT_TYPE = 'PRIMARY KEY'
and     tblConstraint.CONSTRAINT_CATALOG = @databaseName
and     tblConstraint.TABLE_SCHEMA = 'dbo'
and     tblConstraint.TABLE_NAME = 'DimEmployee'
go

Output:

informationSchemaTable

References

Michael StoneBraker et al discuss No SQL, MapReduce, And Traditional DBMS DBMS

Micheal StoneBraker et al discuss No-SQL, MapReduce, And Traditional DBMS

In a series of Web Articles Michael Stonebraker has been discussing NoSQL (MapReduce

and similar technologies).

Though, his points may seem simple dissin these technologies, but also they do talk about

what is good about NoSQL, what is currently lacking, where they are best used, and

overall technical “potholes” to be aware of.

I first became of Michael when Informix bought Illustra.  And, they were talking about

data blades.  Reading about Illustra was my first initiation into Object DBs and how best

to incorporate other data sources into Traditional DB Systems.

A very tiny introduction to Michael StoneBraker’s contribution to Academia and

Information Technology is accessible via Wikipedia (

http://en.wikipedia.org/wiki/Michael_Stonebraker )

Here are some of his noted web postings:

1) Map Reduce & Parallel DBMS – Friends or Foes

http://database.cs.brown.edu/papers/stonebraker-cacm2010.pdf 

2)  The NoSQL Discussion has nothing to do with SQL

http://cacm.acm.org/blogs/blog-cacm/50678-the-nosql-discussion-has-nothing-to-do-with-sql/fulltext