Questions tagged [covering-index]
52 questions
40
votes
4 answers
Mysql covering vs composite vs column index
In the following query
SELECT col1,col2
FROM table1
WHERE col3='value1'
AND col4='value2'
If I have 2 separate indexes one on col3 and the other on col4, Which one of them will be used in this query ?
I read somewhere that for each table…
Songo
- 5,195
- 7
- 51
- 88
15
votes
3 answers
Why use INCLUDE in a SQL index
I recently encountered an index in a database I maintain that was of the form:
CREATE INDEX [IX_Foo] ON [Foo]
( Id ASC )
INCLUDE
( SubId )
In this particular case, the performance problem that I was encountering (a slow SELECT filtering on both Id…
StarLite
- 223
- 2
- 7
7
votes
1 answer
Can I create a "Covering, Spatial" index in SQL Server 2008?
I currently have a site with a table that has Lat/Long float columns, and an index over those 2 columns plus another one I need to retrieve.
I'm constantly querying this table to get the rows that fall within a radius from a certain point (I'm…
Daniel Magliola
- 27,613
- 56
- 154
- 235
6
votes
1 answer
Why PostgresQL count is so slow even with Index Only Scan
I have a simple count query that can use Index Only Scan, but it still take so long in PostgresQL!
I have a cars table with 2 columns type bigint and active boolean, I also have a multi-column index on those columns
CREATE TABLE cars
(
id BIGSERIAL…
Rey
- 83
- 9
6
votes
2 answers
What is the difference between composite non clustered index and covering index
SQL Server 2005 includes "covering index" feature which allows us to select more than one non key column to be included to the existing non clustered index.
For example, I have the following columns:
EmployeeID, DepartmentID, DesignationID,…
Tom
- 61
- 1
- 2
3
votes
3 answers
MongoDB covering index not working
I have a countries document which looks like that:
{
"_id" : ObjectId("4e493af4140700590800154f"),
"geoname_id" : "49518",
"code" : "rw",
"names" : {
"en" : "Rwanda",
"nl" : "Rwanda",
"de" : "Ruanda"
}
}
In order to touch the…
fightbulc
- 140
- 7
3
votes
0 answers
Why did my SQL fail when I added a covering index?
I'm totally confused by this one. I added a covering index to a SQL Server 2017 table and my integration tests broke.
Here is some simple SQL to illustrate the problem:
CREATE TABLE MyColumns (ColumnID INT PRIMARY KEY, LookupCodeTypeID INT)
INSERT…
sixeyes
- 369
- 2
- 12
3
votes
1 answer
Why covering index not used in the case of meeting the conditions?
A covering index is a special case of an index in InnoDB where all required fields for a query are included in the index, as mentioned in this blog https://blog.toadworld.com/2017/04/06/speed-up-your-queries-using-the-covering-index-in-mysql.
But, I…
张拉拉
- 53
- 3
3
votes
1 answer
Overlapping of cover and single indexes in SQL Server
I have a question concerning best-practices for indexing in SQL Server (or any RDBMS for that matter). Take the following table:
ProfileID int
Text nvarchar(50)
ProfileID is joined to a Profile table. For each profile, each Text must be…
IamIC
- 16,207
- 18
- 81
- 142
3
votes
1 answer
Why is PostgreSQL not using *just* the covering index in this query depending on the contents of its IN() clause?
I have a table with a covering index that should respond to a query using just the index, without checking the table at all. Postgres does, in fact, do that, if the IN() clause has 1 or a few elements in it. However, if the IN clause has lots of…
Daniel Magliola
- 27,613
- 56
- 154
- 235
3
votes
1 answer
Query doesn't use a covering-index when applicable
I've downloaded the employees database and executed some queries for benchmarking purposes.
Then I noticed that one query didn't use a covering index, although there was a corresponding index that I created earlier. Only when I added a FORCE INDEX…
Dor
- 6,916
- 4
- 28
- 45
3
votes
2 answers
EF Code First: CreateIndex - Covering Index
Is it possible to use the CreateIndex syntax of EF Code First Migrations to create a Covering Index (*see below for what a Covering Index is).
For example I can create a simple index in a manual migration like so:
CreateIndex("RelatedProduct",…
Chris Moschini
- 33,398
- 18
- 147
- 176
2
votes
1 answer
efficient "find nearest number or date" in SQL where date/number column is covered by an index
Using SQL2008, I'm trying to figure out an efficient query to find a row whose date is nearest to a specific target date.
There are obvious inefficient solutions (e.g. table scan using ABS and DATEDIFF) which I didn't bother looking at, because my…
Justin Grant
- 41,265
- 11
- 110
- 185
2
votes
3 answers
Make MySQL query more performant
I have two tables, users and points. Currently users has 84,263 rows, while points has 1,636,119 rows. Each user can have 0 or multiple points and I need to extract which point was created last.
show create table users
CREATE TABLE `users` (
`id`…
Kristjan O.
- 583
- 1
- 4
- 24
2
votes
1 answer
Does the include order has any impact on covering indexes?
Is there any difference between the two following indexes in PostgreSQL:
CREATE INDEX my_index ON table_name (column_one, column_two)
INCLUDE (account_id, id)
and
CREATE INDEX my_index ON table_name (column_one, column_two)
INCLUDE (id,…
jjacobi
- 315
- 1
- 7