Consider a very large table having hundreds of millions of rows defined as
-- Here ObjectIdName and ObjectTypeName together are unique. -- Due to their size they aren't indexed, but rather they are -- hashed to ObjectIdName -> ObjectId and ObjectTypeName -> ObjectType -- which are used in the index instead. CREATE TABLE VeryLarge ( ObjectId INT NOT NULL, ObjectIdName NVARCHAR(512) NOT NULL, ObjectType INT NOT NULL, ObjectTypeName NVARCHAR(512) NOT NULL, PayLoad VARBINARY(MAX) NULL, IsDeleted BIT NOT NULL ); -- This was originally in the question. By mistake includes the Payload column. CREATE NONCLUSTERED INDEX IX_VeryLarge1 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, PayLoad, ObjectIdName, ObjectTypeName); -- This was the index I intended to have, without the Payload column. CREATE NONCLUSTERED INDEX IX_VeryLarge2 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, ObjectIdName, ObjectTypeName);
If one assumes the
ObjectType parameters locate a single row in 99.99% of the cases, it looks to me there needs to be an interplay between the query and the indexing to be so effective the query will always return predictably in sensible time (considering hardware and the actual amount of rows). However, I'm not sure how should the query be constructed. Basically what I would like to do is query first with
ObjectType and filter this small result set with
ObjectTypeName (if there are more than one row).
Considering this, with the defined indexing, is the following query sensible on SQL Server?
SELECT Payload FROM VeryLarge WHERE ObjectId = @objectId AND ObjectType = @objectType AND IsDeleted = 0 AND ObjectIdName = @objectIdName AND ObjectTypeName = @objectTypeName; -- Or alternatively, considering the question: SELECT PayLoad FROM ( SELECT PayLoad, ObjectIdName, ObjectTypeName FROM VeryLarge WHERE ObjectId = @objectId AND ObjectType = @objectType AND IsDeleted = 0 ) AS x WHERE x.ObjectIdName = @objectIdName AND x.ObjectTypeName = @objectTypeName;
I don't know how to construct such a query that would prune the results of the first query that will is guaranteed to hit an index and return only one or a few rows. It would look that using
INCLUDE has the same effect, forcing and index seek but I don't know if there will be costs associated with this. Also, I know MySQL doesn't have this concept of included index, but it works somehow similarily and I assume filtering the result set of at most a few rows would work on any database that supports indexing regardless of other structures.
This is related to my other question at Hashed and heap indexed object storage table insert and query performance, which has the table and query defined.
<edit: Additional question: Having those repeating
ObjectType columns in the same table looks like wasteful. Might be subject for another post, but I believe they ought to be separated to a different table. Then the question is that should the
INNER JOIN be avoid with a query that does the join only if there are more rows than one after the first filtering. And how to do that.