Opened 12 years ago

Closed 12 years ago

#4609 closed patch (fixed)

Scheduler speedup - SQL with negative values on unsigned int columns.

Reported by: Mark Buechler <Mark.Buechler@…> Owned by: Isaac Richards
Priority: minor Milestone: unknown
Component: mythtv Version: head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

I've noticed a very long running query in the scheduler "DELETE FROM program WHERE manualid = -1 OR (manualid <> 0 AND -1 = -1);" which is invalid due to manualid being defined as an unsigned int (as well as recordid). This, on my system running mysql 5.0.41, takes typically 11 seconds to run. By removing the negative value in the WHERE clause it takes 0 seconds.

Attached is a patch that, instead of doing the check for "RECORDID = -1" in SQL, it does it in code thus eliminating the slow WHERE clause.

There may be more of these but these are the two biggest for me. With this patch, my scheduler runs go from 17 seconds average down to 5-6 seconds average.

Attachments (1)

scheduler.diff (1.2 KB) - added by Mark Buechler <Mark.Buechler@…> 12 years ago.
The patch.

Download all attachments as: .zip

Change History (2)

Changed 12 years ago by Mark Buechler <Mark.Buechler@…>

Attachment: scheduler.diff added

The patch.

comment:1 Changed 12 years ago by gigem

Resolution: fixed
Status: newclosed

(In [15829]) Quick work around for a database inefficiency when comparing unsigned, recordid values to the wildcard, recordid of -1. This appears to cause MySQL to ignore or not be able to use some table indices. This change customizes the affected queries to act on all recordids or the specified one as needed. It is partly based on the patch by Mark Buechler.

An alternative approach to consider for longer term is to change the database schema to use signed recordids. PI::recordid is signed so there's already a lurking problem, though, I don't expect anyone, not even bjm, to ever use > 231 recordids.

Closes #4609.

Note: See TracTickets for help on using tickets.