[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