Improve performance of time search

[dekarl@…]

The SQL query for time search takes 2 minutes over here. By slightly refactoring the query that can be brought down to 2 seconds. Just factor out the description column into a superquery with the actual query being a subquery.

The new query looks like:

select program.description, sub.* from program, (
SELECT DISTINCT program.chanid,
... here is the old query but without the description column ...
) as sub where program.chanid=sub.chanid and program.starttime=sub.starttime;

The reason for the difference is each description taking 48kb of memory no matter how much of that is actually used leading to the whole joining and sorting being done on the hard disk because it doesn't fit into the memory buffer. (at least that's what I suspect)

[dekarl@…]

example of how it could work in the code

[dekarl@…]

bummer, here's the real file. Got to commit before format-patch

[justinh]

The new query was fine but results were being incorrectly assigned - this patch should fix that

[dekarl@…]

updated patch to master/0.26 post UTC

[dekarl@…]

Find attached an updated and retested patch against latest master.

[Karl Dietz <dekarl@…>]

In c0c47344879d3467cfea62938a33e70278252b36/mythtv:

Speed up and reduce temporary space of time search a lot.

By factoring out the description into a super query the performance can
be greatly improved (e.g. from 2 minutes to 2 seconds)

Thanks to sphery for inspiration by [d9fb21853f] and justinh for testing
and fixing my braino with the column order.

Fixes #10023

[Karl Dietz <dekarl@…>]

In 3d7a20ce1008b1a84127f17dea7c75e05cacb4aa/mythtv:

Speed up and reduce temporary space of time search a lot.

By factoring out the description into a super query the performance can
be greatly improved (e.g. from 2 minutes to 2 seconds)

Thanks to sphery for inspiration by [d9fb21853f] and justinh for testing
and fixing my braino with the column order.

Fixes #10023
(cherry picked from commit c0c47344879d3467cfea62938a33e70278252b36)

Conflicts:

mythtv/libs/libmyth/programinfo.cpp