[mythtv-users] Upgraded to Ubuntu 22.04 / MythTV 32 - MythWeb search now abysmally slow

[Will Dormann]

Hi folks,

I recently upgraded my MythTV box to Ubuntu 22.04 / MythTV 32 (from 
Ubuntu 20.04 / MythTV 31) mostly to get support for a new WiFi card, and 
for the most part things went fine.  With the new setup, my prior issues 
with Nvidia picture quality and lirc are now moot, as it's living a 
backend-only life now.

However, one thing that I noticed is that using mythweb to search for 
something (using the search box) is abysmally slow.  mysqld has the CPU 
pegged, and my web browser has been spinning for about 15 MINUTES and 
counting, and it's still going.

I'm sure I've used mythweb search before and had it come back with 
results within 10 seconds or so.  But given the multiple moving parts 
with this recent upgrade, it's hard to track down what the culprit might be.

I did a web search for mythtv slow mysql, and all I found was 
<https://www.mythtv.org/wiki/Tune_MySQL>, which as with seemingly many 
things in the wiki is woefully out of date.  Specifically the sample 
mythtv.cnf file that is provided has about a half-dozen options that 
mysqld no longer recognizes and will refuse to start if they are set.

A glance at mytop shows that the query it's slowly executing is:
SELECT DISTINCT program.*, UNIX_TIMESTAMP(program.starttime) AS 
starttime_unix, UNIX_TIMESTAMP(program.endtime) AS endtime_unix, 
IFNULL(pr1.`system`, "") AS rater, IFNULL(pr1.rating, "") AS rating, 
channel.callsign, channel.channum FROM program USE INDEX (id_start_end) 
LEFT JOIN programrating pr1 on program.chanid = pr1.chanid and 
program.starttime = pr1.starttime LEFT OUTER JOIN programrating pr2 on 
program.chanid = pr2.chanid and program.starttime = pr2.starttime and 
pr2.`system` > pr1.`system` LEFT JOIN channel on channel.chanid = 
program.chanid LEFT JOIN credits on program.chanid = credits.chanid and 
program.starttime = credits.starttime LEFT JOIN people USING (person) 
WHERE pr2.`system` is null and program.chanid IN 
(2002,20003,2003,2004,2005,2007,2009,3918,2013,2016,2017,2018,2019,2021,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2044,2045,2046,2047,20068,2070,20071,2072,2074,2075,20083,20084,20085,2086,2087,2088,2089,2092,20093,2096,2097,2105,2106,20107,2110,2111,20113,2115,3874,20121,2122,2123,2125,2126,20127,2128,2129,2131,2132,2134,2142,20144,2149,2158,20159,2161,2163,20167,2168,20182,2183,20184,2185,20188,2194,2210,20212,20213,20214,3964,2217,20218,20219,20220,20222,20225,2230,2231,2234,2235,20238,2239,2240,3957,20246,2251,20253,20254,2255,2256,2259,20260,20263,20269,2270,20271,2272,20277,2281,2285,2286,20287,21831,20291,2292,2295,20315,20316,3900,2332,20335,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2365,2366,2367,2369,2370,2373,2374,2375,2376,2377,2378,2379,2380,2385,2386,2387,2388,2390,2391,20395,20445,21832,2455,2461,2465,2466,2467,3965,2470,3962,20472,2473,2474,3863,3967,20482,3882,3858,3867,2503,3862,3857,2507,2510,2511,2513,2516,3868,20529,2550,20551,2552,20554,3960,20568,2570,20571,2572,2574,20575,20576,20581,20583,20584,20585,20586,20587,2588,2589,2593,20594,20595,2596,20597,20599,3865,3856,3870,20605,20609,20610,2611,20612,20613,20614,2615,2617,2618,20620,20621,3854,3866,2624,3958,2628,2629,2630,20631,2632,2633,20634,20635,20639,3852,20641,20644,20645,2649,2650,2651,2652,2653,2654,2656,20657,20658,2662,20662,20663,2664,20665,20667,2668,2670,2674,2676,20677,20678,2679,2680,20681,2683,20684,2685,20688,20689,2690,2692,2694,2695,2696,20697,20699,2710,2711,20715,2717,20720,20721,20726,20730,2731,2734,20735,20737,20738,2739,2740,2741,20743,20744,20746,2752,20754,2756,2757,2763,2765,2770,2771,2772,20773,20774,3963,20776,20777,2780,2781,2786,20787,2789,2793,20794,20795,2797,3884,3959,3864,20806,20810,20812,20813,20814,20815,3907,20818,20830,20831,20833,20834,20835,1840,1842,1844,1845,1846,1847,1848,1849,1850,1852,1854,1856,1858,20860,1861,1862,2865,2867,20868,2869,2873,2885,2887,20895,20896,21000,21001,21002,21009,3010,21450,21451,21452,21453,21454,3455,3456,3457,3458,3459,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,21496,21498,21499,3500,21501,21502,21503,21505,21506,21507,21508,21509,3510,21511,21512,21513,21515,21516,21517,3520,3521,21524,21525,21528,21533,3534,21535,21536,21537,21538,21539,3541,3542,3543,3544,3545,3546,3547,21548,21549,21550,21551,21552,21560,21561,21562,3564,3565,21566,21567,21579,3580,21581,21583,21584,21585,21586,21587,21588,21590,3600,21601,3602,21603,3621,3622,3623,21624,3625,3640,21641,21643,3645,3646,21648,3660,3661,21662,21663,21664,21665,21666,21670,21680,21681,21683,21685,21686,21687,21688,21689,21691,21692,21693,21700,3966,21702,21703,21704,21705,3707,21710,21711,21721,21724,21725,3726,3728,3729,21740,21741,21750,3751,21752,21753,21754,21755,21756,21757,21758,21759,21760,21761,21762,21763,21764,21765,21768,21769,21770,21771,21833,21774,21776,21777,21778,21779,21780,21781,21782,21783,21784,21787,21788,21789,21790,21791,21792,21793,21794,21795,21796,21797,3800,21801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3811,3812,3813,3814,3815,3816,3817,3818,3819,3820,3821,21822,21823,3824,3825,3826,3827,21828,3829,21830,3831,3832,3833,3834,3835,3836,3837,3838,3839,3840,3841,3842,3843,3844,3845,3846,3847,3848,3849) 
AND (program.endtime > FROM_UNIXTIME('1695935481') AND program.starttime 
< FROM_UNIXTIME('1698527481') AND program.starttime != program.endtime) 
AND (program.title LIKE '%SearchTerm%' OR program.subtitle LIKE 
'%SearchTerm%' OR program.description LIKE '%SearchTerm%' OR 
program.category LIKE '%SearchTerm%' OR people.name LIKE '%SearchTerm%') 
AND (program.endtime >= NOW()) ORDER BY program.starttime


Any tips as to what my problem might be?  I'd ideally like to avoid 
restoring from backup to 20.04 to confirm if my memory was correct about 
being able to use mythweb search.


Thanks!
-WD