Possibly incorrect index precedence for SQL queries

[erikvk]

@miyconst

I have a database class Mopedo.Database.Device (see below for class definition). I have a couple of indexes on this table:

[{
  "Name": "Device_IP",
  "ResourceName": "Mopedo.Database.Device",
  "Provider": "StarcounterDeclared",
  "Columns": [{
    "Name": "IP",
    "Descending": false
  }]
}, {
  "Name": "Device_UsedCount",
  "ResourceName": "Mopedo.Database.Device",
  "Provider": "StarcounterDeclared",
  "Columns": [{
    "Name": "UsedCount",
    "Descending": true
  }]
}]

My issue is that the index used in ORDER BY clauses in SQL statements have precedence over any index that could be used to evaluate the WHERE clause(s), even though WHERE is evaluated before ORDER BY. This seems wrong to me – and makes some of our queries, like in example 3 below, many times slower.

Example 1

SQL query 1:

SELECT IP FROM Mopedo.Database.Device ORDER BY UsedCount

Query plan 1:

Tables(
 0 = Mopedo.Database.Device
)
Projection(
 0 = 
  StringProperty(0, IP)
)
IndexScan(
 Device_UsedCount ON Mopedo.Database.Device
 0
 UsedCount
 IntegerDynamicRange(
 )
 LogicalValue(TRUE)
 Descending
)

Here the Device_UsedCount index is used, which of course is what we want.

Example 2

SQL query 2:

SELECT IP FROM Mopedo.Database.Device WHERE IP = '127.0.0.1'

Query plan 2:

Tables(
 0 = Mopedo.Database.Device
)
Projection(
 0 = 
  StringProperty(0, IP)
)
IndexScan(
 Device_IP ON Mopedo.Database.Device
 0
 IP
 StringDynamicRange(
  StringRangePoint(
   GreaterThanOrEqual
   StringLiteral(127.0.0.1)
  )
  StringRangePoint(
   LessThanOrEqual
   StringLiteral(127.0.0.1)
  )
 )
 LogicalValue(TRUE)
 Ascending
)

Here the Device_IP index is used, which – again – is what we expect.

Example 3

SQL query 3:

SELECT IP FROM Mopedo.Database.Device WHERE IP = '127.0.0.1' ORDER BY UsedCount

Query plan 3:

Tables(
 0 = Mopedo.Database.Device
)
Projection(
 0 = 
  StringProperty(0, IP)
)
IndexScan(
 Device_UsedCount ON Mopedo.Database.Device
 0
 UsedCount
 IntegerDynamicRange(
 )
 ComparisonString(
  Equal
  StringProperty(0, IP)
  StringLiteral(127.0.0.1)
 )
 Descending
)

Here the Device_UsedCount index takes precedence over Device_IP, which is clearly not the fastest way to evaluate the query. You could argue that ORDER BY should only be used when expecting multiple result rows – but still my conjecture is that using the index on WHERE (especially if the table contains thousands and thousands of rows like in this case) should be more efficient. Please correct me if I'm wrong about this.

Mopedo.Database.Device definition

[Database, RESTar]
public class Device : IDataSource, IDatabaseResource, IEntity
{
    [Key] public string IP { get; }
    public string BrowserUserAgent { get; internal set; }
    public Geo Geo { get; internal set; }
    public bool? DoNotTrack { get; internal set; }
    public string Ifa { get; internal set; }
    public int? DeviceType { get; internal set; }
    public string Make { get; internal set; }
    public string Model { get; internal set; }
    public string OperatingSystem { get; internal set; }
    public string OperatingSystemVersion { get; internal set; }
    public bool? JavaScriptSupported { get; internal set; }
    public string FlashVersionSupported { get; internal set; }
    public string Language { get; internal set; }
    public string Carrier { get; internal set; }
    public int? ConnectionType { get; internal set; }
    public string SHA1HashedDeviceId { get; internal set; }
    public string MD5HashedDeviceId { get; internal set; }
    public string SHA1HashedPlatformId { get; internal set; }
    public string MD5HashedPlatformId { get; internal set; }
    public DateTime? MatchedAt { get; private set; }
    [RESTarMember(readOnly: true)] public DateTime CreatedAt { get; set; }
    [RESTarMember(readOnly: true)] public DateTime UsedAt { get; set; }
    [RESTarMember(readOnly: true)] public long UsedCount { get; set; }
    [RESTarMember(readOnly: true)] public long UsedRank { get; set; }
    public DeviceExtension Extension
    {
        get => Db.SQL<DeviceExtension>(DeviceExtension.ByDevice, this).FirstOrDefault();
        set
        {
            if (value == null) Extension?.Delete();
            else value[nameof(IP)] = IP;
        }
    }
}

[erikvk]

Version=2.3.2.930

[erikvk]

(note that this is not a big problem for us in our apps – I will just add index hints for the problematic queries in my code)

[k-rus]

Thank you for reporting.

@un-tone Can you check the issue?

cc @miyconst

[un-tone]

@k-rus I confirm the issue.

[un-tone]

@bigwad votes to close the issue as won't fix.

cc @miyconst

[miyconst]

@un-tone, @bigwad thank you for checking the issue. I am going to keep it as later and see when/if we can fix it.