Triggers are, unfortunately, not an option as MySQL table gets updated via backup (so it's DROP TABLE -> CREATE TABLE -> INSERT)

Thanks.

In regards to the documentation, I'd like to clarify that it refers to a database page accessible at

SMP → System Operation → Databases, and not SMP → Menu → Configure Databases.

It may be useful as one of the metrics related to the code quality/monitoring.

For example, my continuous integration system Cache GitHub CI tracks compile time for each commit, and if it suddenly grows, then the offending commit may be worth looking into.  But if we add one other metric - "lines of code added", then some of the offending commits may be removed based on a now determinable fact that a raise in compilation time is caused by a project size increase.

On the screenshot: compilation time (Length column) for some commits in a real-life project.

Other usage - find classes longer than, for example, 500 sloc and separate them into several classes.

"val" should be in Upper case I suppose?

No.  IndexOpen calls IndexExists to get object ID.  In IndexExists "val" is matched to corresponding ID with the following SQL expression (except for IDKEYExists. It calls %ExistsId):

SELECT %ID INTO :id 
FROM Package.Class 
WHERE 
  (val IS NOT NULL AND IndexProperty = val) OR 
  (val IS NULL     AND IndexProperty IS NULL)

The interesting question would be - why not traverse index global to get id instead of using SQL?

Sure did. To clarify, it's the fastest way available by default. The fastest way is a direct global reference (provided of course that we do not have the object in a context already available), but we need to either hardcode the global reference or calculate it at compile time. Here's the test class (I won't copy it here - it's fairly long). The results are like this:

Iterations: 10000
Object access: .130111
GetStored access: .014388
SQL access: .020268
Global access: .007717
Object access takes 904.30% of GetStored time
Object access takes 641.95% of SQL time
Object access takes 1686.03% of Global time
GetStored access takes 70.99% of SQL time
GetStored access takes 186.45% of Global time
SQL access takes 262.64% of Global time

Where:

• Object access is opening an object and retrieving the property
• SQL access is embedded SQL
• GetStored is a method discussed in this article
• Global is a direct global reference to a stored property

One piece of code I'd like to share here is the macro to get global reference for a property by a class and property name:

Class Utils.GetStored Extends %Persistent
{

Property text As %String;

ClassMethod Global() As %Status
{
    #Define GetStoredProp(%cls,%prop,%idvar) ##Expression(##class(Utils.GetStored).GetPropGLVN(%cls,%prop, $name(%idvar)))
    Set Id = $Random(999)+1
    Set Val = $$$GetStoredProp("Utils.GetStored","text", Id)
}

/// Write ##class(Utils.GetStored).GetPropGLVN("Utils.GetStored", "text")
ClassMethod GetPropGLVN(Class, Property, IdVar = "id") As %String
{
    Set StoredCode = $Get($$$EXTPROPondisk($$$pEXT,Class,Property))
    Set StoredCode = $Replace(StoredCode, "(id)", "(" _ IdVar _ ")")
    Return StoredCode
}

On compilation the string with $$$GetStoredProp macro would be compiled into:

Set Val = $listget($g(^Utils.GetStoredD(Id)),2) 

This problem can be reproduced on all infinite scroll pages. The particular page I reported is just an example.

 Another question what is the sense to check "val" for IS NULL for Unique Index?

This check, if hit returns first Id with empty val.

So, "val" should exactly match the value of property, case sensitive?

That depends on property collation. For EXACT/ TRUNCATE/SQLSTRING collation, yes "val" should exactly match the value of the property (compared part of the value), case sensitive. For SQLUPPER - no.

Well, that depends.

1. If an index definition includes an explicitly specified collation for a property, the index uses that collation.
2. If an index definition does not include an explicitly specified collation for a property, the index uses the collation explicitly specified in the property definition.
3. If the property definition does not include an explicitly specified collation, then the index uses the collation that is the default for the property data type.
4. If the property data type does not include an explicitly specified collation, then the index uses namespace default collation
5. If the namespace default collation is not specified, then SQLUPPER is used.

I need "u" flag in studio. During compilation the flags from user (studio) are combined with namespace or system flags. So even if I set namespace default flags without "u", the compiler would still use this flag as it is present in studio.

Added a demo class.