Improve EPG speed by adding indices to "program" table (1 Viewer)

Andy_2639

Portal Member
February 25, 2017
25
6
Stuttgart
Home Country
Germany Germany
Started on: 2022-08-28
last update: 2022-08-28

Summary:
Improve EPG speed by adding indices to "program" table

Area:
TvServer SQL database (MySQL/MariaDB)

Description:
I added the following indices to the "program" table:
SQL:
  KEY `idxEndTime` (`endTime`),
  KEY `idxTime` (`startTime`) USING BTREE,
  KEY `idxTitle` (`title`(333))

("USING BTREE" and "(333)" come from the tool I use, HeideSQL 11.3.0.6295)

This lowered the execution time of EPG related queries like

SQL:
select p.* from Program p inner join Channel c on c.idChannel = p.idChannel where ((EndTime > '2022-07-01 18:09:49' and EndTime < '2022-07-11 18:09:49') or (StartTime >= '2022-07-01 18:09:49' and StartTime <= '2022-07-11 18:09:49') or (StartTime <= '2022-07-01 18:09:49' and EndTime >= '2022-07-11 18:09:49')) and c.visibleInGuide = 1 order by startTime;
SQL:
select p.* from Program p inner join Channel c on c.idChannel = p.idChannel where ((EndTime > '2022-08-14 12:47:16' and EndTime < '2022-09-11 12:47:16') or (StartTime >= '2022-08-14 12:47:16' and StartTime <= '2022-09-11 12:47:16') or (StartTime <= '2022-08-14 12:47:16' and EndTime >= '2022-09-11 12:47:16')) and title = 'Knight Rider [adventure/western/war]' and c.visibleInGuide = 1 order by startTime;

from 5 seconds to 0.5 seconds (yes, factor 10). So using the EPG is much more responsive.
I'm using MariaDB 10.5.17.
I changed the engine of the "program" table (and just of that table) from InnoDB to MyISAM (much earlier than the time measurements - at a time I used MySQL; for performance reasons).

As I don't know all queries, this index choice might be suboptimal. Maybe the queries itself could be optimized?
It definitely adds overhead to adding and deleting EPG entries (and takes more disk space).
 

Users who are viewing this thread

Top Bottom