home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 1
Development
Improvement Suggestions
Improve EPG speed by adding indices to "program" table
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="Andy_2639" data-source="post: 1290311" data-attributes="member: 161048"><p>Started on: 2022-08-28</p><p>last update: 2022-08-28</p><p></p><p>Summary:</p><p>Improve EPG speed by adding indices to "program" table</p><p></p><p>Area:</p><p>TvServer SQL database (MySQL/MariaDB)</p><p></p><p>Description:</p><p>I added the following indices to the "program" table:</p><p>[CODE=sql] KEY `idxEndTime` (`endTime`),</p><p> KEY `idxTime` (`startTime`) USING BTREE,</p><p> KEY `idxTitle` (`title`(333))[/CODE]</p><p></p><p>("USING BTREE" and "(333)" come from the tool I use, HeideSQL 11.3.0.6295)</p><p></p><p>This lowered the execution time of EPG related queries like</p><p></p><p>[CODE=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;[/CODE]</p><p>[CODE=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;[/CODE]</p><p></p><p>from 5 seconds to 0.5 seconds (yes, factor 10). So using the EPG is much more responsive.</p><p>I'm using MariaDB 10.5.17.</p><p>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).</p><p></p><p>As I don't know all queries, this index choice might be suboptimal. Maybe the queries itself could be optimized?</p><p>It definitely adds overhead to adding and deleting EPG entries (and takes more disk space).</p></blockquote><p></p>
[QUOTE="Andy_2639, post: 1290311, member: 161048"] 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: [CODE=sql] KEY `idxEndTime` (`endTime`), KEY `idxTime` (`startTime`) USING BTREE, KEY `idxTitle` (`title`(333))[/CODE] ("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 [CODE=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;[/CODE] [CODE=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;[/CODE] 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). [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 1
Development
Improvement Suggestions
Improve EPG speed by adding indices to "program" table
Contact us
RSS
Top
Bottom