Opened 9 years ago
Closed 8 years ago
Last modified 8 years ago
#10023 closed Bug Report - General (fixed)
Improve performance of time search
Reported by: | Owned by: | Karl Egly | |
---|---|---|---|
Priority: | minor | Milestone: | 0.26.1 |
Component: | MythTV - General | Version: | Master Head |
Severity: | medium | Keywords: | |
Cc: | Ticket locked: | no |
Description
The SQL query for time search takes 2 minutes over here. By slightly refactoring the query that can be brought down to 2 seconds. Just factor out the description column into a superquery with the actual query being a subquery.
The new query looks like:
select program.description, sub.* from program, ( SELECT DISTINCT program.chanid, ... here is the old query but without the description column ... ) as sub where program.chanid=sub.chanid and program.starttime=sub.starttime;
The reason for the difference is each description taking 48kb of memory no matter how much of that is actually used leading to the whole joining and sorting being done on the hard disk because it doesn't fit into the memory buffer. (at least that's what I suspect)
Attachments (4)
Change History (10)
Changed 9 years ago by
Attachment: | 0001-Ignore-channels-without-xmltvid-when-guessing-if-the.patch added |
---|
Changed 9 years ago by
Attachment: | 0002-Speed-up-time-search-by-factoring-out-the-descriptio.patch added |
---|
bummer, here's the real file. Got to commit before format-patch
comment:1 Changed 9 years ago by
Owner: | set to sphery |
---|---|
Status: | new → assigned |
Changed 9 years ago by
Attachment: | speeduptimequery.patch added |
---|
The new query was fine but results were being incorrectly assigned - this patch should fix that
Changed 9 years ago by
Attachment: | 0007-Speed-up-and-reduce-temporary-space-of-time-search-a.patch added |
---|
updated patch to master/0.26 post UTC
comment:3 Changed 8 years ago by
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
comment:4 Changed 8 years ago by
Milestone: | unknown → 0.27 |
---|---|
Owner: | changed from sphery to Karl Egly |
comment:6 Changed 8 years ago by
Milestone: | 0.27 → 0.26.1 |
---|
example of how it could work in the code