[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