CODEWITHSUNDEEP
mysqlindexinginformation-schema
SexyMF
SexyMF

Reputation: 11185

Can Mysql information_schema not be aligned with show create table?

There seems to be data missing from information_schema. the show create table is different from information_schema.

when i run: SHOW CREATE TABLE devicoserver.email_templates;

I get 4 keys:

CREATE TABLE `email_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language_id` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`),
  KEY `email_templates_language_id_foreign` (`language_id`),
  KEY `email_templates_email_type_unique` (`email_type`),
  KEY `email_templates_subject_unique` (`subject`),
  CONSTRAINT `email_templates_language_id_foreign` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

But, when I query the information schema it indicates that there are only 2 keys

    select 
         CONSTRAINT_NAME,COLUMN_NAME FROM
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'email_templates' and CONSTRAINT_SCHEMA = 'devicoserver';

PRIMARY, id
email_templates_language_id_foreign,language_id

Does this make sense, Can information_schema be different then the actuall table? I know its actual because I have tried to create a key and i got exception that it is already existed.

Thanks

Upvotes: 2

Views: 315

Answers (1)

Paul Campbell
Paul Campbell

Reputation: 1986

You should query information_schema.statistics

e.g.

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) 
FROM `information_schema`.`statistics` 
WHERE table_schema = 'dbname' 
AND TABLE_NAME = 'tablename' 
GROUP BY INDEX_NAME 
ORDER BY INDEX_NAME ASC;

Upvotes: 3

Related Questions