[mythtv] patch: progfind speedup

[Andy Davidoff]

This trivial patch speeds up the program finder by
several orders of magnitude via dramatic reduction
of the search space.  Apply the additional index to
further improve search times.
-------------- next part --------------
Index: MC/libs/libmythtv/progfind.cpp
===================================================================
RCS file: /var/lib/cvs/MC/libs/libmythtv/progfind.cpp,v
retrieving revision 1.14
diff -d -u -w -r1.14 progfind.cpp
--- MC/libs/libmythtv/progfind.cpp	25 Feb 2003 07:52:24 -0000	1.14
+++ MC/libs/libmythtv/progfind.cpp	2 Mar 2003 21:31:14 -0000
@@ -1004,9 +1004,8 @@
     if (searchData[curSearch] == "T")
     {
     thequery = QString("SELECT title "
-                       "FROM program,channel "
+                       "FROM channel left join program using (chanid) "
                        "WHERE program.title LIKE '%1%' AND program.title NOT LIKE 'The %' "
-		       "AND program.chanid = channel.chanid "
                        "AND program.starttime > %2 "
                        "GROUP BY program.title "
                        "ORDER BY program.title;")
@@ -1015,8 +1014,8 @@
     else
     {
     thequery = QString("SELECT title "
-                       "FROM program,channel "
-                       "WHERE program.title LIKE '%1%' AND program.chanid = channel.chanid "
+                       "FROM channel left join program using (chanid) "
+                       "WHERE program.title LIKE '%1%' "
 		       "AND program.starttime > %2 "
 		       "GROUP BY program.title "
                        "ORDER BY program.title;")
@@ -1231,8 +1230,8 @@
 
     thequery = QString("SELECT subtitle,starttime,channel.channum,"
 		       "channel.callsign,description,endtime,channel.chanid "
-                       "FROM program,channel "
-                       "WHERE program.title = \"%1\" AND program.chanid = channel.chanid "
+                       "FROM channel left join program using (chanid) "
+                       "WHERE program.title = \"%1\" "
 		       "AND program.starttime > %2 "
                        "ORDER BY program.starttime;")
                         .arg(progTitle.utf8()).arg(progStart.toString("yyyyMMddhhmm50"));
@@ -1370,8 +1369,8 @@
     QString data;
 
     thequery = QString("SELECT title "
-                       "FROM program,channel "
-                       "WHERE program.title LIKE '%1%' AND program.chanid = channel.chanid "
+                       "FROM channel left join program using (chanid) "
+                       "WHERE program.title LIKE '%1%' "
 		       "AND program.starttime > %3 "
 		       "GROUP BY program.title "
                        "ORDER BY program.title LIMIT %2;")
@@ -1408,8 +1407,8 @@
     {
 
     thequery = QString("SELECT title "
-                       "FROM program,channel "
-                       "WHERE program.title LIKE '%1%' AND program.chanid = channel.chanid "
+                       "FROM channel left join program using (chanid) "
+                       "WHERE program.title LIKE '%1%' "
 		       "AND program.starttime > %3 "
                        "GROUP BY program.title "
                        "ORDER BY program.title DESC LIMIT %2;")
-------------- next part --------------
alter table program add index (chanid, title);