summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorMichael T. Dean <mdean@mythtv.org>2011-08-27 21:00:08 (GMT)
committer Michael T. Dean <mdean@mythtv.org>2011-08-29 23:26:14 (GMT)
commit1de043119cac1cdae3a418a5b4203a7fce58f514 (patch)
tree5646384249dfac37f9b2d4e88eedf5f3d55a473c
parent4942639cfe30dcd7c73b4ad4f15a5f2593474da1 (diff)
Fix inefficiency in marking first/last showings without programids.
For users without programids (where listings provider doesn't specify a programid and doesn't specify both season and episode number), mythfilldatabase marks first/last showings by comparing title, subtitle, and description to find unique episodes. If the listings provider provides extremely long descriptions (>>255 characters), this results in mysqld creating on-disk temp tables, and can create extremely long run times for this specific query (with one user reporting times on the order of 30min using an AMD Athlon II 5050e with 2GB RAM). This change limits the query to use only the first 1024 characters of description for the SELECT and the GROUP BY. This is equivalent to what we were doing before ccb759a946 changed the description from TEXT to VARCHAR, since MySQL automatically uses max_sort_length (defaults to 1024) for sorting text and blob fields (including GROUP BY). Even in the event of 2 shows that differ only after the first 1024 characters of the description, but that air at the same time, the logic is sound. In the event 2 shows with different start times that differ only after the first 1024 characters of the description exist in the listings, one won't be properly marked as first/last showing, but I'd hope that listings providers would provide some indication of differences before the end of the novel (but, again, this is the same behavior we would have seen before the change to use VARCHAR for description). Thanks to Jan Schneider for noticing the issue and performing a bunch of test queries to help me find a way to work around it. (cherry picked from commit 8b0daaf01f00605a424e95e55d0d62ab60827d70)
-rw-r--r--mythtv/programs/mythfilldatabase/main.cpp18
1 files changed, 10 insertions, 8 deletions
diff --git a/mythtv/programs/mythfilldatabase/main.cpp b/mythtv/programs/mythfilldatabase/main.cpp
index 5886b3f..6520714 100644
--- a/mythtv/programs/mythfilldatabase/main.cpp
+++ b/mythtv/programs/mythfilldatabase/main.cpp
@@ -885,9 +885,10 @@ int main(int argc, char *argv[])
}
}
int found = query.size();
- query.prepare("SELECT MIN(starttime),title,subtitle,description "
+ query.prepare("SELECT MIN(starttime),title,subtitle,"
+ " LEFT(description, 1024) AS partdesc "
"FROM program WHERE programid = '' "
- "GROUP BY title,subtitle,description;");
+ "GROUP BY title,subtitle,partdesc;");
if (query.exec())
{
while(query.next())
@@ -896,11 +897,11 @@ int main(int argc, char *argv[])
"WHERE starttime = :STARTTIME "
" AND title = :TITLE "
" AND subtitle = :SUBTITLE "
- " AND description = :DESCRIPTION");
+ " AND LEFT(description, 1024) = :PARTDESC");
updt.bindValue(":STARTTIME", query.value(0).toDateTime());
updt.bindValue(":TITLE", query.value(1).toString());
updt.bindValue(":SUBTITLE", query.value(2).toString());
- updt.bindValue(":DESCRIPTION", query.value(3).toString());
+ updt.bindValue(":PARTDESC", query.value(3).toString());
if (!updt.exec())
MythDB::DBError("Marking first showings", updt);
}
@@ -925,9 +926,10 @@ int main(int argc, char *argv[])
}
}
found = query.size();
- query.prepare("SELECT MAX(starttime),title,subtitle,description "
+ query.prepare("SELECT MAX(starttime),title,subtitle,"
+ " LEFT(description, 1024) AS partdesc "
"FROM program WHERE programid = '' "
- "GROUP BY title,subtitle,description;");
+ "GROUP BY title,subtitle,partdesc;");
if (query.exec())
{
while(query.next())
@@ -936,11 +938,11 @@ int main(int argc, char *argv[])
"WHERE starttime = :STARTTIME "
" AND title = :TITLE "
" AND subtitle = :SUBTITLE "
- " AND description = :DESCRIPTION");
+ " AND LEFT(description, 1024) = :PARTDESC");
updt.bindValue(":STARTTIME", query.value(0).toDateTime());
updt.bindValue(":TITLE", query.value(1).toString());
updt.bindValue(":SUBTITLE", query.value(2).toString());
- updt.bindValue(":DESCRIPTION", query.value(3).toString());
+ updt.bindValue(":PARTDESC", query.value(3).toString());
if (!updt.exec())
MythDB::DBError("Marking last showings", updt);
}