Questions tagged [covering-index]
56 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,618
- 8
- 58
- 96
16
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
- 233
- 3
- 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
- 30,898
- 61
- 164
- 243
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
- 2
- 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
4
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…
张拉拉
- 73
- 3
4
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
- 30,898
- 61
- 164
- 243
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
- 8
3
votes
3 answers
Postgres Slow Query on large table
I am trying to reduce the query execution time of the query given below. It joins 3 tables to get the data from very big Postgres tables, I have tried to introduce all the necessary indexes on relevant tables but still, the query is taking too long.…
TechnoBasant
- 31
- 4
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
- 483
- 3
- 14
3
votes
1 answer
Why does multi-columns indexing in SQLite slow down the query's performance, unless indexing all columns?
I am trying to optimize the performance of a simple query to a SQLite database by using indexing. As an example, the table has 5M rows, 5 columns; the SELECT statement is to pick up all columns and the WHERE statement checks for only 2 columns.…
Hieu Tran
- 31
- 4
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
- 17,747
- 20
- 91
- 154
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
- 7,344
- 4
- 32
- 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
- 36,764
- 19
- 160
- 190
2
votes
3 answers
Postgis not using index scan only
I'm trying to make Postgis using an index scan only but instead it's performing a Bitmap index scan into a Bitmap heap scan.
I got the following table - containing 50k rows:
CREATE TABLE IF NOT EXISTS public.forme_iris
(
code_iris character…
Bil11
- 85
- 1
- 9