[mythtv] Re: mythweb program listing 'out of memory'.
Martin Moeller
martin at martinm-76.dk
Sun Mar 16 16:25:55 EST 2003
Okay, here goes (it took a while to find the option to log queries, but
here it is):
On looking it over, it seems that 'order by channum + 0' is recurring
quite a bit? Perhaps the value should be grabbed from the database (also
making me not have to update functions all the time :))
The 'bad' query:
030316 16:18:21 2 Connect mythtv at localhost on
2 Init DB mythconverg
2 Query SELECT * FROM channel ORDER BY chanid
2 Query select count(*) as 'recordings' from record
2 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316161550 AND program.endtime > 20030316161550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
2 Quit
The good (working) query:
030316 16:21:16 3 Connect mythtv at localhost on
3 Init DB mythconverg
3 Query SELECT * FROM channel ORDER BY channum + 0
3 Query select count(*) as 'recordings' from record
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316161550 AND program.endtime > 20030316161550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316164550 AND program.endtime > 20030316164550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316171550 AND program.endtime > 20030316171550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316174550 AND program.endtime > 20030316174550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316181550 AND program.endtime > 20030316181550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
3 Query SELECT sum(record.type = 4 and program.title =
record.title)>0 as 'type4', sum(record.type = 3 and program.title =
record.title and record.chanid = program.chanid)>0 as 'type3',
sum(record.type = 2 and program.title = record.title and record.chanid =
program.chanid and record.starttime =
sec_to_time(time_to_sec(program.starttime)) and record.endtime =
sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', sum(record.type
= 1 and program.title = record.title and record.chanid = program.chanid
and record.starttime = sec_to_time(time_to_sec(program.starttime))
and record.startdate = from_days(to_days(program.starttime)))>0 as
'type1', channel.chanid, channel.channum, program.starttime,
program.endtime, program.title, program.subtitle, program.description,
program.category, program.airdate, program.stars,
concat(repeat('♦', program.stars*4), if((program.stars*4*10) % 10,
'½', '')) as starstring, ifnull(programrating.system, '') as
rater, ifnull(programrating.rating, '') as rating,
((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) /
60 ) as duration FROM channel left join program using (chanid) left join
programrating using (chanid, starttime) , record WHERE program.starttime
< 20030316184550 AND program.endtime > 20030316184550 GROUP BY
program.chanid ORDER BY channel.channum + 0, programrating.system
030316 16:21:17 3 Quit
søn, 2003-03-16 kl. 05:28 skrev Andy Davidoff:
> If possible, please have your MySQL server output to a logfile and
> then send me the queries that are executed by the process.
>
> Since the channel list is not generated from the same database
> query as the program listings, it's entirely possible that you
> have a 2nd video-source and that this is causing the listing
> query to be processed strangely.
>
> You haven't said if the problem exists in the listings or movies
> pages (or both). Please send us more details about your setup and
> the data in your database.
>
>
> #if Martin Moeller /* Mar 16, 02:03 */
> > Can anyone see why getting the channels in a different order would
> > increase the memory usage (and not just a little but a
> > "whoa-that's-a-big-kind-of-change' lot)...
> #endif /* martin at martinm-76.dk */
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at snowman.net
> http://www.snowman.net/mailman/listinfo/mythtv-dev
--
Martin Moeller <martin at martinm-76.dk>
More information about the mythtv-dev
mailing list