<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<br>
<div class="moz-forward-container">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
I was looking through the MythTV mysql database to try to<br>
figure out where the current position in a recording is stored.<br>
Based on the name, I thought <b>recordedseek</b> might be the
spot.<br>
<br>
<blockquote><tt>mysql> <b>describe recordedseek;</b></tt><br>
<tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
<tt>| Field | Type | Null | Key |
Default | Extra |</tt><br>
<tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
<tt>| chanid | int(10) unsigned | NO | PRI |
0 | |</tt><br>
<tt>| starttime | datetime | NO | PRI |
0000-00-00 00:00:00 | |</tt><br>
<tt>| mark | mediumint(8) unsigned | NO | PRI |
0 | |</tt><br>
<tt>| <font color="#ff0000">offset | bigint(20) unsigned</font>
| NO | | NULL | |</tt><br>
<tt>| type | tinyint(4) | NO | PRI |
0 | |</tt><br>
<tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
<tt>5 rows in set (0.00 sec)</tt><br>
<br>
<br>
<tt>mysql> <b>select * from recordedseek;</b></tt><br>
<tt>+--------+---------------------+--------+-------------+------+</tt><br>
<tt>| chanid | starttime | mark | offset | type
|</tt><br>
<tt>+--------+---------------------+--------+-------------+------+</tt><br>
<tt>| 1111 | 2010-04-04 05:00:00 | 44794 | 2181271880 | 9
|</tt><br>
<tt>| 1111 | 2010-04-04 05:00:00 | 44781 | 2180506720 | 9
|</tt><br>
<tt>| 1111 | 2010-04-04 05:00:00 | 44766 | 2179733476 | 9
|</tt><br>
</blockquote>
...<br>
<br>
This went on forever, until I typed Ctrl-C to terminate the
operation.<br>
<br>
<blockquote><tt>| 1091 | 2009-01-17 21:00:00 | 49644 |
3156051316 | 9 |</tt><br>
<tt>| 1091 | 2009-01-17 21:00:00 | 49644 | 3156051316 | 9
|</tt><br>
<tt>Ctrl-C -- sending "KILL QUERY 52407" to server ...</tt><br>
<tt>^CCtrl-C -- query aborted.</tt><br>
<br>
<tt>+--------+---------------------+--------+-------------+------+</tt><br>
<tt><b><font color="#ff0000">2280198</font></b> rows in set
(3.00 sec)</tt><br>
</blockquote>
<br>
This message rather surprised me. I've got 2.28 million <b>recordedseek</b>
rows<br>
in my database? Granted, I currently have a large number of
recorded shows (234),<br>
but that's averaging 9744 recordedseek instances per recording,
which is ridiculous.<br>
<br>
It seems likely that a large number of <b>recordedseek</b>
instances are not being<br>
deleted from the database when the corresponding recording is
deleted. Some<br>
of the dates listed above are from 2009, so I've been accumulating
them for at<br>
least 6 years.<br>
<br>
Do other developers / users have really large numbers of these
rows in<br>
their databases?<br>
<br>
You can retrieve an approximate count using the following syntax:<br>
<br>
<blockquote><tt>mysql> <b>show table status like
'recordedseek';</b></tt><br>
<tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
<tt>| 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 |</tt><br>
<tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
<tt>| recordedseek | MyISAM | 10 | Fixed | <b><font
color="#ff0000">2280198</font></b> | 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
| | |</tt><br>
<tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
<tt>1 row in set (0.00 sec)</tt><br>
<br>
<tt>mysql></tt> <br>
</blockquote>
Any theories on why these keep accumulating?<br>
<br>
The size of this database table worries me. I wonder if it's
slowing down<br>
my system performance. Lately, my recording playback has been<br>
pausing for 2 or 3 seconds every time I hit the top of an hour,
even<br>
when there is no recording currently going on. Would the insertion<br>
of another instance of <b>recordedseek</b> every hour cause such
a delay<br>
due to the size of the table?<br>
<br>
- Steve<br>
<br>
<br>
<br>
</div>
<br>
</body>
</html>