[mythtv] Mythtv database indices

[Gary Buhrmaster]

On Tue, Jul 14, 2020 at 9:08 PM Peter Bennett <pb.mythtv at gmail.com> wrote:

> I noticed that leanfront was taking a long time to get its list of
> recordings, so I ran "top" on the backend database machine while
> leanfront was loading. I found 100% CPU utilization for 40-odd seconds.
> If you suspect there is a similar issue you can try that. Another thing
> you can do is run "show full processlist" against the database while you
> are starting your frontend. This will show the queries that are running
> and how long they are taking. This helped me to find the cause of the
> problem.

One can (often should for debugging purposes) turn on
slow query logging in ones database server to identify
the first set of potential requests to examine further,
realizing that some queries will be slow, and should be
ignored (the poster child is some of the final steps in
mythfilldatabase for large lineups/schedules where it
updates a lot of rows, and is, not surprisingly, slow).

And, of course, it is best practice for any developer to
run an "explain" on any newly created query to make
sure the query plan optimizer is doing what one
expects (some types of queries can result in the most
interesting results, and of course the results can
change after updating the database statistics (query
optimizers are interesting; often compared to black
magic (correlated subqueries are the ban of most of
their existances))).