Search code examples
mysqldatabaseindexinginnodb

Why index size in innodb is zero?


I have database and set the engine to innodb, The question is: Why the index size is zero in innodb , while I change the engine to myisam, the index has size? ... I'm afraid that index isn't run, because I have this when run mysqltuner: Adjust your join queries to always utilize indexes

Edit: That is show create table:

    CREATE TABLE `campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` mediumtext NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `fcategory` int(11) DEFAULT NULL,
  `scategory` int(11) DEFAULT NULL,
  `imp_per_day` int(11) DEFAULT NULL,
  `imp_in_today` int(11) NOT NULL,
  `ad_serving_pace` tinyint(1) DEFAULT NULL COMMENT '(1,2) = (EVEN, FAST)',
  `target_platform` tinyint(1) NOT NULL COMMENT '(1,2) = (Android, iOS)',
  `language` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '(1,2,3,4) = (running, paused, completed, deleted)',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8

and It's table status: table status


Solution

  • In MyISAM, every index is a separate BTree, including the PRIMARY KEY.

    In InnoDB, the PRIMARY KEY is "clustered" with the data in the same BTree. Meanwhile, each secondary key is a separate BTree.

    So, "Index_size=0" on an InnoDB table means that you have no secondary keys.

    Let's see SHOW CREATE TABLE and the SELECT -- to see whether you have the 'best' indexes.

    (Caveat: This answer does not necessarily cover FULLTEXT and SPATIAL indexes.)