[mythtv-users] What does this SQL query do in MythWeb ?

[Niels Dybdahl]

On 3/4/06, Niels Dybdahl <niels at dybdahl.dk> wrote:
>
> Hi,
>
> I have just upgraded two systems to 0.19.
>
> On one of them MythWeb is slow to show program listings (not recordings)
> and I have found out that it does 246 SQL-queries similar (WHERE clause
> differs) to the following for each listing page:
>
>                       SELECT program.*,
>                          UNIX_TIMESTAMP(program.starttime) AS
> starttime_unix,
>                          UNIX_TIMESTAMP( program.endtime) AS endtime_unix,
>                          CONCAT(repeat('&diams;', program.stars * '4'),
>                                 IF((program.stars * '4' * 10) % 10,
>                                    "&frac12;", "")) AS starstring,
>                          IFNULL(programrating.system, "") AS rater,
>                          IFNULL(programrating.rating, "") AS rating,
>                          oldrecorded.recstatus,
>                          channel.channum
>                   FROM program
>                        LEFT JOIN programrating USING (chanid, starttime)
>                        LEFT JOIN oldrecorded
>                                  ON oldrecorded.recstatus IN (-3, 11)
>                                     AND IF(oldrecorded.programid OR
> oldrecorded.seriesid,
>                                            oldrecorded.programid =
> program.programid
>                                              AND oldrecorded.seriesid =
> program.seriesid,
>                                            oldrecorded.title =
> program.title
>                                              AND oldrecorded.subtitle =
> program.subtitle
>                                              AND oldrecorded.description =
> program.description
>                                           )
>                        LEFT JOIN channel ON program.chanid =
> channel.chanid
>                  WHERE program.chanid='1003' AND program.starttime =
> FROM_UNIXTIME('1141943100') GROUP BY program.chanid, program.starttime
> The other system is faster to show program listings and it only executes
> 29 of these queries for each page, even though it has more channels (the
> first has 9 channels, the second around 100).
> So I would like to know what this query does and/or how I can control its
> execution.
>

I have looked into the php-code and it seems that the query above is
executed once for each scheduled recording in the database, even if the
schedules is so far ahead that they are not going to be shown on the
listings page.

So this could be optimized significantly, by limiting it to schedules within
the time frame needed for the program listing page.

Niels Dybdahl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20060305/d195e24b/attachment.htm