[mythtv-users] SQL strategy (was Re: No Upcoming Recordings)

chris at cpr.homelinux.net chris at cpr.homelinux.net
Mon Oct 9 20:52:29 UTC 2006


On Mon, Oct 09, 2006 at 03:51:24PM -0400, James Pifer wrote:
>  SELECT data FROM settings WHERE value = 'DBSchemaVer' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'DBSchemaVer' AND hostname IS NULL;
>  SELECT data FROM settings WHERE value = 'BackendServerPort' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'BackendStatusPort' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'BackendServerIP' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'MasterServerIP' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'MasterServerIP' AND hostname IS NULL;
>  SELECT data FROM settings WHERE value = 'LogEnabled' AND hostname = 'mythtv-be' ;
>  SELECT data FROM settings WHERE value = 'LogEnabled' AND hostname IS NULL;

I've often wondered if it wouldn't be easier/faster/safer if Myth 
was to do a "SELECT value,data FROM settings WHERE hostname IS NULL 
OR hostname='mythtv-be';" and read/filter the results in memory 
rather than asking the SQL server to parse and execute 100-150 
individual SELECT statements.  Half of these queries probably 
return no results anyway, so the extra SQL parsing time is wasted.

It may not make much difference with the settings table where the 
total read time is less than a second for either method, but I 
assume that this practise of using a separate query for each line 
of data is probably used elsewhere where aggregate searches would 
offer a significant performance improvement.

Is it done this way to mimimize the memory footprint of 
mythfrontend and avoid swap, or did things just happen this way by 
accident?



More information about the mythtv-users mailing list