Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used?

For MySQL one could for instance check for the existance using SHOW CREATE TABLE mytable. In the result there would be something like this if column mycolumn has an index: KEY 'Index_1' ('mycolumn').

Is this indicator KEY unified among all SQL database systems?

Are there better ways to check for an index?

share|improve this question

4 Answers 4

up vote 5 down vote accepted

I'm afraid not.

The ANSI SQL standard defines some data dictionary views in a schema ("INFORMATION_SCHEMA") that can be used to look for specific types of objects (tables, views, table columns), but it doesn't include anything regarding indexes.

Most RDBMSes have their own internal data dictionary views that expose this information (sysindexes in SQL Server & Sybase, DBA_INDEXES in Oracle, for example).

share|improve this answer
    
Thank you. I guess I'll have to create database-specific checks for the databases that may be used in our application. –  Björn Jacobs Apr 24 at 13:08

When it comes to MySQL, you need to search the INFORMATION_SCHEMA database.

The following tables will allow you to hunt down a column through all your indexes.

TABLE DESCRIPTIONS

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

mysql> show create table information_schema.key_column_usage\G
*************************** 1. row ***************************
       Table: KEY_COLUMN_USAGE
Create Table: CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` (
  `CONSTRAINT_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT '0',
  `POSITION_IN_UNIQUE_CONSTRAINT` bigint(10) DEFAULT NULL,
  `REFERENCED_TABLE_SCHEMA` varchar(64) DEFAULT NULL,
  `REFERENCED_TABLE_NAME` varchar(64) DEFAULT NULL,
  `REFERENCED_COLUMN_NAME` varchar(64) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

INFORMATION_SCHEMA.STATISTICS

mysql> show create table information_schema.statistics\G
*************************** 1. row ***************************
       Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLLATION` varchar(1) DEFAULT NULL,
  `CARDINALITY` bigint(21) DEFAULT NULL,
  `SUB_PART` bigint(3) DEFAULT NULL,
  `PACKED` varchar(10) DEFAULT NULL,
  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
  `COMMENT` varchar(16) DEFAULT NULL,
  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

For example, you can search for the indexes with a column Column_Name_I_Want like this:

EXAMPLE #1

SELECT table_schema DB,table_name TBL,
    constraint_name NDX,
    seq_in_index NDX_POS,position_in_unique_constraint CONST_POS
FROM information_schema.key_column_usage WHERE column_name = 'Column_Name_I_Want'
AND table_schema NOT IN ('information_schema','mysql','performance_schema');

EXAMPLE #2

SELECT
    table_schema DB,table_name TBL,
    index_name NDX,seq_in_index NDXPOS
FROM information_schema.statistics WHERE column_name = 'Column_Name_I_Want'
AND table_schema NOT IN ('information_schema','mysql','performance_schema');

CAVEAT

Please note that these types of queries will be slow because MySQL's INFORMATION_SCHEMA

  • are MEMORY table without indexes
  • are slow to access InnoDB
    • with lots of tables
    • when opening file handles just to read metadata
    • with InnoDB tables accessing its metadata
  • are slow to access partitioned MyISAM tables with hundreds of partitions
  • are fast to access non-partitioned MyISAM tables
share|improve this answer
    
Thank you for your detailed answer. My main problem though is that our application is supposed to support multiple database systems - not only MySql. At one point there should be given out a warning if indexes on important columns are missing in the database. This check should work with all database systems of a selection. But thanks again, your answer will come in useful in the MySql-part. –  Björn Jacobs Apr 24 at 16:02

There is no cross-vendor implementation of indexes. Indexes are not even mentioned in the ANSI/ISO SQL specification.

Indexes are a physical optimization to help speed up queries, but the usage and behavior of indexes is left as an implementation detail (which is another way of saying that each RDBMS vendor decides how to support indexes, and they are under no obligation to do it in the same way). It's actually pretty amazing that statements like CREATE INDEX are as similar as they are between implementations.

So don't expect there to be consistent usage of words like INDEX or KEY, or any consistent way of querying index metadata.

It's pretty much a myth that one can write 100% portable SQL code that runs identically across any implementation of SQL. You have to compromise, and limit yourself to a short list of brands of database that you will test and support.

share|improve this answer

Check results instead of indexes

If your application requires certain indexes to run fast enough, then a check for that would be a set of certain benchmark queries - if they run well, then the current indexes are sufficient; and if they don't, then a fix is needed.

If your application requires certain indexes to run correctly (i.e., ensure uniqueness or foreign keys), and you can't control those indexes yourself, then again the best solution would be a set of unit-tests to verify that the behavior of that database is as required.

This approach would work an all possible setups, give useful information also for systems you haven't considered and stay relevant if some major database starts to offer different, incompatible kinds of indexes that won't be detected in the same way.

In short, look at the results, not at how that system achieved them.

share|improve this answer
    
Checking results is not the best indicator of the presence or absence of an index. Why? Even if an index on a column is present, lopsided key distribution will cause a query optimizer to ignore an index even if the index has every column you used in WHERE, ORDER BY, and GROUP BY clauses. Please see me posts dba.stackexchange.com/questions/63047/… and dba.stackexchange.com/questions/27097/… as examples. –  RolandoMySQLDBA Apr 24 at 18:24
    
@RolandoMySQLDBA The whole point is to ask the question why do you need to check for that particular property (existence of a specific index) on a system that you don't control and don't know the exact platform/setup? If the index exists but isn't used (as you describe), causing unacceptable performance - then your check shouldn't say "ah, I see an index, everything is good"; it should require a fix. And if the performance is ok, then it doesn't matter if some index doesn't exist or that particular DB system has weird indexing that you can't detect. –  Peteris Apr 24 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.