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:
("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
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).
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).