[mythtv] Why are there so many instances of recordedseek?

[Nick Morrott]

On 21 April 2015 at 01:09, Steve E <simon.sinister at sbcglobal.net> wrote:
>
> I was looking through the MythTV mysql database to try to
> figure out where the current position in a recording is stored.
> Based on the name, I thought recordedseek might be the spot.

> mysql> select * from recordedseek;

<snip>

> 2280198 rows in set (3.00 sec)

> This message rather surprised me. I've got 2.28 million recordedseek rows
> in my database? Granted, I currently have a large number of recorded shows
> (234),
> but that's averaging 9744 recordedseek instances per recording, which is
> ridiculous.

recordedseek stores locations of all keyframes in recorded video - not
your current bookmark - so that number seems perfectly normal. You can
have several keyframes per second of video, depending on the
compression. You'd certainly have more keyframes if you have longer
than average recordings, but one might expect the average number of
entries per recorded hour of video to be /roughly/ the same for all
users.

> It seems likely that a large number of recordedseek instances are not being
> deleted from the database when the corresponding recording is deleted. Some
> of the dates listed above are from 2009, so I've been accumulating them for
> at
> least 6 years.
>
> Do other developers / users have really large numbers of these rows in
> their databases?

100+ recordings currently. 900K entries. No orphans.

> You can retrieve an approximate count using the following syntax:
>
> mysql> show table status like 'recordedseek';
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> | Name         | Engine | Version | Row_format | Rows    | Avg_row_length |
> Data_length | Max_data_length  | Index_length | Data_free | Auto_increment |
> Create_time         | Update_time         | Check_time          | Collation
> | Checksum | Create_options | Comment |
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> | recordedseek | MyISAM |      10 | Fixed      | 2280198 |             25 |
> 61642050 | 7036874417766399 |     67974144 |   4637100 |           NULL |
> 2014-12-06 17:48:53 | 2015-04-19 23:49:30 | 2015-04-18 07:42:10 |
> utf8_general_ci |     NULL |                |         |
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> 1 row in set (0.00 sec)


You can get an exact count using:

mysql> SELECT COUNT(*) FROM `recordedseek`;


> Any theories on why these keep accumulating?

They should be deleted when you delete the recording. Maybe you've
lost recordings in the past and the entries became orphaned then?

You can find the start times of recordedseek entries that do not
relate to current recordings using some brute force:

SELECT DISTINCT `starttime`
FROM `recordedseek`
WHERE `starttime` NOT IN (SELECT `starttime` FROM `recorded`)
ORDER By `starttime` ASC;

I get an empty result set in this case.

Cheers,
Nick