Opened 8 years ago

Closed 4 years ago

#10447 closed Patch - Feature (Fixed)

Program Listing SQL Optimization

Reported by: fracmak@… 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 8 years ago by fracmak@…

I've made a pull request for this change

https://github.com/MythTV/mythtv/pull/16

comment:2 Changed 8 years ago by sphery

Owner: set to sphery
Status: newassigned
Type: Developer TaskPatch - Feature

comment:3 Changed 6 years ago by Gary Buhrmaster <gary.buhrmaster@…>

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 4 years ago by Karl Egly

Milestone: unknown0.28
Owner: changed from sphery to Karl Egly
Status: assignedaccepted

Closing as I merged it as 7817b55789462dca3e6d1a4ee0f6e1606ad77677 six months ago.

comment:5 Changed 4 years ago by Karl Egly

Resolution: Fixed
Status: acceptedclosed
Note: See TracTickets for help on using tickets.