index-digest

events.log

$ index_digest mysql://foo:bar@server/stats
------------------------------------------------------------
Found 5 issue(s) to report for "stats" database
------------------------------------------------------------
MySQL v5.6.24-72.2-log at statsdb-s2
index-digest v0.1.0
------------------------------------------------------------
redundant_indices → table affected: events

✗ "user_id_idx" index can be removed as redundant (covered by "for_editcount_idx")

  - redundant: KEY user_id_idx (user_id)
  - covered_by: KEY for_editcount_idx (user_id, page_ns, event_type)
  - schema: CREATE TABLE `events` (
      `wiki_id` int(8) unsigned NOT NULL,
      `page_id` int(8) unsigned NOT NULL,
      `rev_id` int(8) unsigned NOT NULL,
      `log_id` int(8) unsigned NOT NULL DEFAULT '0',
      `user_id` int(8) unsigned NOT NULL,
      `user_is_bot` enum('N','Y') DEFAULT 'N',
      `page_ns` smallint(5) unsigned NOT NULL,
      `is_content` enum('N','Y') DEFAULT 'N',
      `is_redirect` enum('N','Y') DEFAULT 'N',
      `rev_timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `image_links` int(5) unsigned NOT NULL DEFAULT '0',
      `video_links` int(5) unsigned NOT NULL DEFAULT '0',
      `total_words` int(4) unsigned NOT NULL DEFAULT '0',
      `rev_size` mediumint(7) unsigned NOT NULL DEFAULT '0',
      `wiki_lang_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `wiki_cat_id` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `event_type` tinyint(2) unsigned NOT NULL DEFAULT '1',
      `event_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `media_type` tinyint(2) unsigned NOT NULL DEFAULT '0',
      `rev_date` date NOT NULL DEFAULT '0000-00-00',
      `beacon_id` varchar(32) NOT NULL DEFAULT '',
      `ip_bin` varbinary(16) DEFAULT NULL,
      PRIMARY KEY (`wiki_id`,`page_id`,`rev_id`,`log_id`,`rev_timestamp`),
      KEY `event_date_idx` (`event_date`),
      KEY `page_ns_idx` (`page_ns`),
      KEY `rev_timestamp_idx` (`rev_timestamp`),
      KEY `user_id_idx` (`user_id`),
      KEY `wiki_cat_id_idx` (`wiki_cat_id`),
      KEY `wiki_id_idx` (`wiki_id`),
      KEY `wiki_lang_id_idx` (`wiki_lang_id`),
      KEY `for_editcount_idx` (`user_id`,`page_ns`,`event_type`),
      KEY `for_admin_dashboard_idx` (`wiki_id`,`event_date`),
      KEY `for_wikia_api_last_editors_idx` (`wiki_id`,`rev_timestamp`),
      KEY `for_lookup_contribs_idx` (`wiki_id`,`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE (YEAR(rev_timestamp))
    (PARTITION ev2002 VALUES LESS THAN (2002) ENGINE = InnoDB,
     PARTITION ev2003 VALUES LESS THAN (2003) ENGINE = InnoDB,
     PARTITION ev2004 VALUES LESS THAN (2004) ENGINE = InnoDB,
     PARTITION ev2005 VALUES LESS THAN (2005) ENGINE = InnoDB,
     PARTITION ev2006 VALUES LESS THAN (2006) ENGINE = InnoDB,
     PARTITION ev2007 VALUES LESS THAN (2007) ENGINE = InnoDB,
     PARTITION ev2008 VALUES LESS THAN (2008) ENGINE = InnoDB,
     PARTITION ev2009 VALUES LESS THAN (2009) ENGINE = InnoDB,
     PARTITION ev2010 VALUES LESS THAN (2010) ENGINE = InnoDB,
     PARTITION ev2011 VALUES LESS THAN (2011) ENGINE = InnoDB,
     PARTITION ev2012 VALUES LESS THAN (2012) ENGINE = InnoDB,
     PARTITION ev2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
     PARTITION ev2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
     PARTITION ev9999 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
  - table_data_size_mb: 69776.46875
  - table_index_size_mb: 235039.8125

------------------------------------------------------------
redundant_indices → table affected: events

✗ "wiki_id_idx" index can be removed as redundant (covered by "PRIMARY")

------------------------------------------------------------
not_used_indices → table affected: events

✗ "PRIMARY" index was not used by provided queries

  - not_used_index: PRIMARY KEY (wiki_id, page_id, rev_id, log_id, rev_timestamp)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "page_ns_idx" index was not used by provided queries

  - not_used_index: KEY page_ns_idx (page_ns)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "rev_timestamp_idx" index was not used by provided queries

  - not_used_index: KEY rev_timestamp_idx (rev_timestamp)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "wiki_cat_id_idx" index was not used by provided queries

  - not_used_index: KEY wiki_cat_id_idx (wiki_cat_id)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "wiki_id_idx" index was not used by provided queries

  - not_used_index: KEY wiki_id_idx (wiki_id)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "wiki_lang_id_idx" index was not used by provided queries

  - not_used_index: KEY wiki_lang_id_idx (wiki_lang_id)

------------------------------------------------------------
not_used_indices → table affected: events

✗ "for_lookup_contribs_idx" index was not used by provided queries

  - not_used_index: KEY for_lookup_contribs_idx (wiki_id, user_id)

------------------------------------------------------------
not_used_columns → table affected: events

✗ "rev_date" column was not used by provided queries

  - column_type: date
  - column_name: rev_date

------------------------------------------------------------
not_used_columns → table affected: events

✗ "beacon_id" column was not used by provided queries

  - column_type: varchar(32)
  - column_name: beacon_id