[mythtv-users] Slow MySQL query after delete
Tom Metro
tmetro+mythtv-users at gmail.com
Wed Sep 5 06:08:20 UTC 2007
crs23 wrote:
> I have empirical evidence which I've posted that shows the query is not
> quick for me. I get over 3600 rows in about 30 seconds...
Here's another data point:
3324 rows in set (3.04 sec)
3374 rows affected (3.13 sec)
The first was ran yesterday and output to the terminal (which took a
while). The second was ran today, and output to a file, which was 1.1 MB
in size. Repeating the query produces identical numbers.
After running optimize_mythdb.pl:
3374 rows affected (2.32 sec)
So about a 30% improvement.
And the explain:
+----+-------------+--------------+--------+----------------------+----------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+--------------+--------+----------------------+----------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | cardinput | ALL | NULL | NULL
| NULL | NULL
| 2 | Using temporary;
Using filesort |
| 1 | SIMPLE | capturecard | eq_ref | PRIMARY |
PRIMARY | 4 | mythconverg.cardinput.cardid
| 1 | Using index
|
| 1 | SIMPLE | record | ALL | PRIMARY | NULL
| NULL | NULL
| 288 |
|
| 1 | SIMPLE | recordmatch | ref | recordid |
recordid | 5 | mythconverg.record.recordid
| 1 | Using where
|
| 1 | SIMPLE | channel | eq_ref | PRIMARY |
PRIMARY | 4 | mythconverg.recordmatch.chanid
| 1 | Using where
|
| 1 | SIMPLE | program | eq_ref | PRIMARY,id_start_end |
PRIMARY | 16 |
mythconverg.recordmatch.chanid,mythconverg.recordmatch.starttime,mythconverg.recordmatch.manualid
| 1 | |
| 1 | SIMPLE | oldrecstatus | eq_ref | PRIMARY,title |
PRIMARY | 160 |
mythconverg.channel.callsign,mythconverg.recordmatch.starttime,mythconverg.program.title
| 1 | |
| 1 | SIMPLE | oldrecorded | ref | title,programid |
title | 130 | mythconverg.program.title
| 15 |
|
| 1 | SIMPLE | recorded | ref | programid,title |
title | 130 | mythconverg.program.title
| 7 |
|
| 1 | SIMPLE | oldfind | eq_ref | PRIMARY |
PRIMARY | 8 | mythconverg.recordmatch.recordid,func
| 1 | Using index
|
+----+-------------+--------------+--------+----------------------+----------+---------+---------------------------------------------------------------------------------------------------+------+---------------------------------+
10 rows in set (0.00 sec)
I've been following this thread with interest, as the MythTV client I
use, mvpmc, started suffering from timeouts after performing a delete
once my database grew in size, typically requiring a restart of the client.
-Tom
More information about the mythtv-users
mailing list