We're updating the issue view to help you get more done. 

Missing index on series column in mh_search

Description

mh_search has a series_id column, which should be indexed because SearchServiceDatabaseImpl.java runs the query Search.getNoSeries which queries for series missing a series id. Without this index, the db does a full table scan.

If you have a lot of episodes without a series, this could also be a performance concern (for every startup) regardless of the index.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> show create table mh_search; | mh_search | CREATE TABLE `mh_search` ( `id` varchar(128) NOT NULL, `series_id` varchar(128) DEFAULT NULL, `organization` varchar(128) DEFAULT NULL, `deletion_date` datetime DEFAULT NULL, `access_control` mediumtext, `mediapackage_xml` mediumtext, `modification_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_mh_search_organization` (`organization`), CONSTRAINT `FK_mh_search_organization` FOREIGN KEY (`organization`) REFERENCES `mh_organization` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | mysql> explain select id from mh_search where series_id is null; +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | mh_search | ALL | NULL | NULL | NULL | NULL | 17296 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ mysql> alter table mh_search add index mh_search_series_ix (series_id); mysql> explain select id from mh_search where series_id is null; +----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | mh_search | ref | mh_search_series_ix | mh_search_series_ix | 387 | const | 52 | Using where; Using index | +----+-------------+-----------+------+---------------------+---------------------+---------+-------+------+--------------------------+

Steps to reproduce

None

Status

Assignee

Unassigned

Reporter

Stephen Marquard

Criticality

None

Tags (folksonomy)

None

Components

Fix versions

Affects versions

2.3.1
6.4

Priority

Major