ElasticSearch – Logstash – SQL Server

Background

Elastic’s Logstash is an ETL tool that allows us to “Request, Collect, Parse, and Send” Data.

Logstash

Definition

Elastic

Here is Elastic’s own definition

Link

definition_logstash_20180808_0125PM

Artifact

Logstash is available here.

The current version is 6.3.2 and it is a very recent release ( 2018-July-24th).

artifact_6_3_2

Download

For MS Windows, please choose the ZIP Version.

Extract

Please extract the compressed file.

JDBC Driver

Microsoft SQL Server

Download

Please download Microsoft SQL Server JDBC Driver from here.

Extract

Extract the downloaded file.

Usage

Files

Configuration File

Outline

  1. Input
    • jdbc
      • Connection String
        • Syntax :- jdbc:sqlserver://[host]:[port-number]
        • Sample :- jdbc:sqlserver://localhost:1433
      • jdbc_user :- database user
      • jdbc_password :- database user passsword
      • jdbc_driver_library :- full file name of JDBC Driver
      • jdbc_driver_class
        • com.microsoft.sqlserver.jdbc.SQLServerDriver
      • statement
        • select top 10 * from [StackOverflow2010].[dbo].[Users] tblU order by tblU.[Id] asc
  2. Output
    • elasticSearch
      • hosts
        • Syntax :- Elastic Search host and Port
        • Sample :- localhost:9200
      • Index
        • Sample :- stackoveflow2010user
      • Document Type
        • Sample :- _doc
      • document_id
        • Syntax :- %{[column-name]}
        • Sample :- %{[id]}

Configuration

input {
  jdbc {
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433"
    # The user we wish to execute our statement as
    jdbc_user => "stackoverflow"
    jdbc_password => "hIy8jA2lNl"
    # The path to our downloaded jdbc driver
    jdbc_driver_library => "C:\Downloads\Microsoft\Java\jdbc\v6.0.8112.200\extract\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    # our query
    statement => "select * from [StackOverflow2010].[dbo].[Users] tblU order by tblU.[Id] asc"
    }
  }
output {
  stdout { codec => json_lines }
  elasticsearch {
  "hosts" => "localhost:9200"
  "index" => "stackoveflow2010user"
  "document_type" => "_doc"
  document_id => "%{[id]}"
  }
}

Command File


setlocal

REM 2018-08-08 11:16 AM Daniel Adeniji ( dadeniji)
REM SET JAVA_HOME to Version 1.8
set "JAVA_HOME=C:\Program Files\Java\jdk1.8.0_181"

set "_binfolder=C:\Downloads\Elastic\Logstash\v6.3.2\extract\bin"
set "_configuration=stackOverflow2010.User.conf"

call %_binfolder%\logstashImpl.bat -f %_configuration%

endlocal

Processing

Script


stackOverflow2010.User.cmd

Output

Output – 01

processing_20180808_0132PM

Output – 02

processing_20180808_0134PM

Output – 03

processing_20180808_0136PM

Output – 04

processing_20180802_011PM

 

Validation

Tools

Postman

Queries

Query – Microsoft

Objective

Find matches for Microsoft

Query


http://localhost:9200/stackoveflow2010user/_doc/_search?q=Microsoft

Design

query_Microsoft_20180808_0117PM

Output

Microsoft_Result_20180808_0118PM

 

References

  1. Elastic
    • Docs
      • Logstash
        • Running Logstash from the Command Line
          Link
    • Blog
      • Suyog Rao
        • Little Logstash Lessons: Handling Duplicates
          Link
  2. StackOverflow
    • Logstash to Keep Two Databases Synced – Cannot Access %{document_id}
      Link
  3. QBox
    • Vineeth Mohan
      • Migrating MySql Data Into Elasticsearch Using Logstash
        Link

 

MySQL – Index Statistics

Background

Wanted to cover how to review Index Statistics in MySQL.

Overview

The tables that we will be using are:

  1. mysql.innodb_table_stats
  2. mysql.innodb_index_stats

Code

 

Item Column Explanation Sample
Database mysql.innodb_index_stats.database_name Database sakila
table mysql.innodb_index_stats.table_name Table actor, film, inventory
Index mysql.innodb_index_stats.index_name Index Name PRIMARY, idx_fk_film_id, idx_store_id_film_id
Stat Name mysql.innodb_index_stats.stat_name Statistics Name n_diff_pfx01
n_diff_pfx02
n_diff_pfx03
Stat Columns mysql.innodb_index_stats.stat_description (
where stat_name=n_diff_pfxNN, the stat_description column shows a comma separated list of the index columns that are counted. )
Key Columns store_id
store_id,film_id
store_id,film_id,inventory_id
Stat Value mysql.innodb_index_stats.stat_value Number of Unique Values in index
Number of Pages Sampled mysql.innodb_index_stats.sample_size Number of pages sampled
Number of Pages Total mysql.innodb_index_stats.stat_value ( mysql.innodb_index_stats.stat_description = ‘Number of pages in the index’ ) Number of Pages total
Sample % mysql.innodb_index_stats.stat_value (stat.name like ‘n_diff_pfx%’ )
 / mysql.innodb_index_stats.stat_value ( stat.name = ‘size’ )
# of Rows mysql.innodb_table_stats.n_rows Number of records in table
Table Size (
INFORMATION_SCHEMA.TABLES.data_length
+ INFORMATION_SCHEMA.TABLES.index_length
)
Table Size from INFORMATION_SCHEMA. TABLES
(
mysql.innodb_table_stats.clustered_index_size
+ mysql.innodb_table_stats.sum_of_other_index_sizes
)
*
(
@@innodb_page_size
)
Table Size from mysql.innodb_table_stats.

Data is availed as Number of Pages.

Index Size (
mysql.innodb_index_stats.stat_value
* @@innodb_page_size
)where ( mysql.innodb_index_stats.stat_name = ‘size’ )
Index Size from mysql.innodb_index_stats.

Information logged is Number of pages.

Filter on stat_name = ‘size’.

Stats Update TS mysql.innodb_index_stats.last_update Last time statistics was updated
Page Size ( @@innodb_page_size / 1024 ) 16 KB

SQL


-- Create database dbUtility if it does not exist
CREATE database IF NOT EXISTS dbUtility;

-- use database dbUtility
use dbUtility;

-- Change delimiter from default of ; to //
delimiter //


DROP PROCEDURE IF EXISTS listIndexStatsInnoDB;
//

create procedure listIndexStatsInnoDB
(
      `database`   nvarchar(100) 
    , `table`      nvarchar(100)
    , `index`      nvarchar(100)
)
begin
    
    /*
     * database :- database
     * table    :- table
     * index    :- index
     */


	select
			  tblIS.database_name as 'database'
			
			, tblIS.table_name as 'table'
			
			, tblIS.index_name as 'index'
			
			, tblIS.stat_name as 'statName'
			
			, tblIS.stat_description as 'statColumns'		
			
			, tblIS.stat_value as 'statValue'
			
			, tblIS.sample_size as 'sampleSize'
			
			, tblISSP.stat_value as 'numberofPages'

			, truncate
			   (
			     case
			        when (tblISSP.stat_value > 0) 
			          then ( tblIS.sample_size * 100.00)
			            / 
			            nullif
			            (
			                tblISSP.stat_value
			               ,0 
			            )
			        else null
			     end
			     , 2
			   ) as '%Sample'
			   
			-- , tblISS.cardinality as 'cardinality'
	
			-- , tblIST.TABLE_ROWS as '#OfRows'
			, tblITS.n_rows as '#ofRows'

			-- using information.schema_tables
			, concat
			  (
			     round
			     (
			         (
			            ( 
			                tblIST.data_length 
			              + tblIST.index_length 
			            )
			            * 1 
			            /
			            (
			              1024 * 1024 
			            )
			         )   
			       , 2
			     )
			     ,' MB'
			  ) 'tableSizeInMBThruIST'
			
			-- using mysql.innodb_table_stats  
			, concat
			  (
			     round
			     (
			         (
			            ( 
			                tblITS.clustered_index_size 
			              + tblITS.sum_of_other_index_sizes 
			            )
			            * @@innodb_page_size
			            /
			            (
			              1024 * 1024 
			            )
			         )   
			       , 2
			     )
			     ,' MB'
			  ) 'tableSizeInMBThruINNODB'

			  
			, concat
			  (
			     round
			     (
			          (tblISSP.stat_value * @@innodb_page_size)
			         /(1024 * 1024 ) 
			       , 2
			     )
			     ,' MB'
			  ) 'indexSizeInMB'

			, date_format
			  (
			     tblIS.last_update
			     , '%Y-%m%-%d %H:%i %p'
			  ) as 'tsUpdate'
			  
			, concat
			  (
			  		truncate
			  		(
			      		( @@innodb_page_size / 1024 )
			      		, 0
			      	)
			      	, ' KB'
			  ) as '@@innodb_page_sizeInKB'

			  
	from   mysql.innodb_index_stats tblIS
 	
	left outer join mysql.innodb_index_stats tblISSP
	
		on   tblIS.database_name = tblISSP.database_name
		and  tblIS.table_name   = tblISSP.table_name
		and  tblIS.index_name   = tblISSP.index_name
		and  tblISSP.stat_name  = 'size'


/*		
	inner join information_schema.STATISTICS tblISS
	
		on   tblIS.database_name = tblISS.table_schema
		and  tblIS.table_name    = tblISS.table_name
		and  tblIS.index_name    = tblISS.index_name

*/
		
	inner join information_schema.TABLES tblIST
	
		on   tblIS.database_name = tblIST.table_schema
		and  tblIS.table_name    = tblIST.table_name
		
		
	inner join mysql.innodb_table_stats tblITS
	
		on   tblIS.database_name = tblITS.database_name
		and  tblIS.table_name    = tblITS.table_name
		
    where  tblIS.database_name = `database`

    and    (
    
                 ( tblIS.table_name = `table` )
              or (
                       ( `table` is null )
                    or ( `table` = '')
                 )
              
           )
           
           
    and    (
    
                 ( tblIS.index_name = `index` )
              or (
                       ( `index` is null )
                    or ( `index` = '')
                 )
              
           )
 
          
 
    /* Get Only the Top Index Column */       
    -- and  tblISS.SEQ_IN_INDEX = 1      


    and  tblIS.stat_name like 'n_diff_pfx%'
   
    /* Filter out Page Information */       
	/*
	    and  tblIS.stat_description not in
			 (
			      'Number of leaf pages in the index'
			    , 'Number of pages in the index'
			 )
	
	*/
    
    order by       
    
             tblIS.table_name
             
           , case
                 when tblIS.index_name = 'PRIMARY' then ''
                 else tblIS.index_name
             end
             
           , tblIS.stat_name 

    ;


end

//

 

Output

sakila.inventory

Image

Explanation

  1. Index :- Primary
    • Column Names :- inventory_id
    • Stat Value :- 4581
    • Sample Size :- 10 pages
    • Number of Pages :- 11
    • Size :- 0.17 MB
  2. Index :- idx_fk_film_id
    • Column Names :- film_id,inventory_id
    • Statistics
      • n_diff_pfx01
        • film_id
          • Stat :- 958
      • n_diff_pfx02
        • film_id,inventory_id
          • Stat :- 4581
    • Sample Size :- 4 pages
    • Number of Pages :- 5
    • Size :- 0.08 MB
  3. Index :- idx_store_id_film_id
    • Column Names :- store_id,film_id,inventory_id
    • Statistics
      • n_diff_pfx01
        • store_id
          • Stat :- 2
      • n_diff_pfx02
        • store_id,film_id
          • Stat :- 1521
    • Sample Size :- 4 pages
    • Number of Pages :- 7
    • Size :- 0.11 MB

 

Source Code Control

GitHub

DanielAdeniji/MySQL.IndexStatsInnoDB

Link

 

References

  1. MySQL
    • Reference
      • Statistics
        • Configuring Persistent Optimizer Statistics Parameters
          Link
      • Analyze
        • Innodb Analyze Table Complexity
          Link
      • INFORMATION_SCHEMA
        • INFORMATION_SCHEMA STATISTICS Table
          • Statistics Table
            Link
        • INFORMATION_SCHEMA COLUMNS Table
          • Columns Table
            Link
  2. Maria DB
    • System Tables
      • mysql.system_tables
        • mysql.innodb_index_stats
          • Home » Resources » Knowledge Base » Library » MariaDB Documentation » SQL Statements & Structure » SQL Statements » Administrative SQL Statements » System Tables » The mysql Database Tables » mysql.innodb_index_stats
            Link
  3. Index
    • Index Size
      • About MySQL
        • How to calculate a specific InnoDB Index Size
          Link

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 :- Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”

Background

As I tested out the code from our last blog, discovered a nice MySQL safety net.

BTW, that blog is here.

 

Error

Error Text

The error reads:


You are using safe update mode and you tried to update a table 
without a WHERE that uses a KEY column

 

Error Image

Troubleshooting

Review code and identify DML operations ( Update, Delete ) where the key column is not being filtered on.

Here are possible scenarios that will raise this error :-

  1. Delete from [table];
  2. Delete from [table] where [id] > 0;

 

Remediation

In our case we replaced


delete

from tblIndexColumn

with


truncate table tblColumn;

Workaround

SQL_SAFE_UPDATES


SET SQL_SAFE_UPDATES=0;

call listIndexColumns();

SET SQL_SAFE_UPDATES=1;

MySQL – Stored Procedure – List Index Columns

Background

Wanted to start writing Stored Procedures against MySQL Databases.

Since we started talking about Indexes in MySQL, thought that will be

familiar grounds to start from.

 

Code

listIndexColumns

 


-- Create database dbUtility if it does not exist
CREATE database IF NOT EXISTS dbUtility;

-- use database dbUtility
use dbUtility;

-- Change delimiter from default of ; to //
delimiter //


DROP PROCEDURE IF EXISTS listIndexColumns;
//


-- Create Stored Procedure listIndexColumns
-- Parameter is dbname
create procedure listIndexColumns
(
	dbname nvarchar(100) 
)
begin

    /*
		Declare local variables
    */
	declare varID integer;
    declare varIDMax integer;
    
    declare varDBName NVARCHAR(100);
    declare varDBNameCurrent NVARCHAR(100);
    
    declare varTable NVARCHAR(100);
    declare varIndex NVARCHAR(100);
    declare varColumnList NVARCHAR(4000);


	declare varSeq integer;
    declare varColumn Nvarchar(100);
    declare varColumnID integer;
    declare varColumnIDMax integer;
 
    declare varColSeparator varchar(30);
    
    /*
		Drop Temporary tables if left over from previous run
    */
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;

    /*
       Create temporary table tblIndex
    */
	create temporary table tblIndex
	(
		id int not null 
		   auto_increment 
		   primary key

		 , tableName varchar(255) not null
		  
		 , indexName varchar(255) not null 
	
         , columnList varchar(4000) null
         
    )
	;

    /*
       Create temporary table tblIndex Column
    */
	create temporary table tblIndexColumn
	(
		   columnID   smallint not null 

         , columnName nvarchar(100) null
         
    )
	;

    /*
		set separator
    */
	set varColSeparator := ' / ';
        
    -- get Current Database
	set varDBNameCurrent := database();
    
    /*
		If dbName is passed in, then please use it
    */
    if (
			   ( dbName is null) 
            or ( dbName = '')
	   ) then 		
        
		set varDBName = varDBNameCurrent;        

   else
   
		set varDBName = dbName;
    
    end if;
    
    /*
		Get Tables & Index in current database
    */
	insert into tblIndex
	(
		   tableName 
		 , indexName  
	)
	select distinct
				TABLE_NAME
			  , iNDEX_NAME

	from   INFORMATION_SCHEMA.STATISTICS

	where  TABLE_SCHEMA = varDBName

    order by
    
		   	TABLE_NAME
            
		  ,	case 
              when INDEX_NAME = 'PRIMARY' then ''
              else INDEX_NAME
            end  
            
		  , iNDEX_NAME
   ;


    /* Reset table counter */
	set varID := 1;
    
    /* Count number of Tables / Indexes */
    set varIDMax := ( 
                        select max(id) 
                        
                        from tblIndex
					);

    /* Reset column list */
    set varColumnList = null;

    /*
		Iterate Tables & Indexes in current database
    */
	WHILE  varID <= varIDMax DO

        /*
			Get Table and Index Name into local variable
        */
		select 
              tableName, indexName
        
        into  varTable, varIndex
        
        from  tblIndex tblI
        
        where  tblI.id = varID
        ;

        /*
			Clear Temporary table use for caching
        */
        
        /*
			delete 
			
			from   tblIndexColumn
            
			;
        
        */
        
        truncate table tblIndexColumn;
        

        /*
			Get Columns for current table / index
        */
		insert into tblIndexColumn
		(
		      columnID 
            , columnName
		)
		select 
                     tblS.SEQ_IN_INDEX
                   , tblS.COLUMN_NAME
 		 
		from   INFORMATION_SCHEMA.STATISTICS tblS

		where  TABLE_SCHEMA = varDBName

		and    TABLE_NAME   = varTable

		and    INDEX_NAME   = varIndex
		;
        

    
		/*
			Reset Column ID
        */
		set varColumnID := 1;

        /*
			Reset Column List
        */
		set varColumnList := '';

    
		/*
			Get Number of Columns for Context Table / Index
        */
		set varColumnIDMax := 
                ( 
					select max(columnID) 
                
					from tblIndexColumn
				);

        /*
			Iterate Column List
        */
		while (varColumnID <= varColumnIDMax) do
			
		
            set varColumn := null;
            
			select 
					columnName
			INTO 
					varColumn
				   
			from   tblIndexColumn tblIC

			where  tblIC.columnID = varColumnID
		
			;  		
	   
			/*
				Add Column to Column List
			*/
			set varColumnList := Concat(
											  varColumnList
                                              
                                            , varColumn
                                            
                                            , case
                                                when (varColumnID < varColumnIDMax )
                                                     then varColSeparator
                                                else ''
                                              end  
										);


			/*
				Update Column Pointer
			*/
			set varColumnID := varColumnID + 1;
			
			
        end while
        ;
        

		/*
			Save Column List
		*/
		update tblIndex
          
        set    columnList = varColumnList
          
        where  id = varID
		; 
          
		
		/*
			Go to next table/index
        */
	    SET varID := varID + 1;
  

	END WHILE;
  
  
    /*
		Get resultset
    */
    select 
    	   tblI.id

		 , tblI.tableName 
		  
		 , tblI.indexName
	
         , tblI.columnList

    from   tblIndex tblI
   
	;
    
    
    /*
		Clean up - Drop Temporary Tables
    */
    
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;


end;

//


 

Lab


set @database = 'sakila';

call dbUtility.listIndexColumns(@database);

 

GitHub

Availed through GitHub here:

DanielAdeniji/MySQL.listIndexColumns
Link

 

Listening

Listening to my favorite person in the world when I am trying…

Joshua Radin
No Envy, No Fear
Link

 

Lyrics

Some are reaching, few are there
Want to reign from a hero’s chair
Some are scared to fly so high
Well this is how we have to try

When your sister turns to leave
Only when she’s most in need
Take away the cause of pain
By showing her we’re all the same

Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

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