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

[Mark Buechler <Mark.Buechler@…>]

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.

[Mark Buechler <Mark.Buechler@…>]

The patch.

[gigem]

(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 > 2^{31 recordids.}

Closes #4609.