Possibly incorrect index precedence for SQL queries #440
Description
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 UsedCountQuery 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 UsedCountQuery 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;
}
}
}