[mythtv-users] Very slow mythfrontend, mysql at 100%

[Dan Wilga]

At 8:09 PM +0100 11/19/09, GXL.nl wrote:
>I dug in a little deeper and when I run this query direclt from 
>mysql (phpmyadmin) it takes 13 secs but if I remove the two fields 
>from oldrecstatus it only takes 3 secs. For this test I have 
>disabled the cache with SQL_NO_CACHE and I have only removed the two 
>fields (oldrecstatus.recstatus, oldrecstatus.reactivate) but have 
>kept the JOIN on that table.

In my experience, there should be no speed difference if all you did 
was remove two fields from the SELECT part of the statement. It's 
usually necessary to remove a JOIN or sub-select.

The other two things that can have a huge effect on speed are missing 
indices on JOINed fields and different character collations (latin1 
vs. UTF-8, for instance) on fields being compared or JOINed.

I just ran this on my machine, with the parts you removed, and it 
took 746 mS to return 9400 rows.

Perhaps if you give me the output of EXPLAIN for this statement, 
something will jump out. Search and replace "sched_temp_record" with 
"record" for this to work. A few indices I found that may help are:

ALTER TABLE  `cardinput` ADD INDEX (  `sourceid` );
ALTER TABLE  `cardinput` ADD INDEX (  `cardid` );
ALTER TABLE  `channel` ADD INDEX (  `sourceid` );

One other thing: Where is your "tmpdir", as set in /etc/my.cnf? This 
query operates on a temporary table which can be quite large, and 
uses another temporary table for sorting. If your tmp_table_size is 
too low and you haven't set a tmpdir, then mysql may be writing to a 
slow (USB, perhaps?) disk.
-- 
Dan Wilga                                                        "Ook."