Opened 12 years ago
Closed 9 years ago
#10447 closed Patch - Feature (Fixed)
Program Listing SQL Optimization
Reported by: | Owned by: | Karl Egly | |
---|---|---|---|
Priority: | minor | Milestone: | 0.28 |
Component: | MythTV - General | Version: | Master Head |
Severity: | low | Keywords: | |
Cc: | Ticket locked: | no |
Description
I've been playing with this change on my local mythweb with enormous (2 to 3x) performance improvements (Ticket #10047). Currently, when a query is made on the program listing table, the best suited index is as follows:
id_start_end (chanid, starttime, endtime)
but if the chanid range gets to be too large, the server could pick a different query. Sometimes it'll use the PRIMARY(chanid, starttime, manualid) or starttime(starttime) indexes. None of these though take into account endtime.
Because of the fact we only use starttime < endtime as a limit on the starttime field, means a large number of unnecessary entries need to be loaded to check the endtime. This becomes incredibly apparent when you try and load more than 70 channels on my system, at which point the performance gets significantly worse.
To get around this, I put a max cap on the starttime entry, since this is the field most likely to be used in the index. According to my own calculations, no program lasts longer than 12 hours on my system, but I made the query cap 24 hours just to be safe.
Adding this cap has resulted in a substantial jump in performance.
Change History (5)
comment:1 Changed 12 years ago by
comment:2 Changed 12 years ago by
Owner: | set to sphery |
---|---|
Status: | new → assigned |
Type: | Developer Task → Patch - Feature |
comment:3 Changed 11 years ago by
With the presumption that the OP has updated their statistics, and MythTV really is choosing a poor path (one would need to run an EXPLAIN to verify), it might be preferable to add a "USE INDEX (id_start_end)" hint to the sql if this really is the preferred path. (FORCE INDEX if the table scan would really be terrible).
comment:4 Changed 9 years ago by
Milestone: | unknown → 0.28 |
---|---|
Owner: | changed from sphery to Karl Egly |
Status: | assigned → accepted |
Closing as I merged it as 7817b55789462dca3e6d1a4ee0f6e1606ad77677 six months ago.
comment:5 Changed 9 years ago by
Resolution: | → Fixed |
---|---|
Status: | accepted → closed |
I've made a pull request for this change
https://github.com/MythTV/mythtv/pull/16