Stack Overflow
Stack Overflow

Questions tagged [covering-index]

56 questions
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
  • 44,807
  • 15
  • 124
  • 208
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.
  • 814
  • 1
  • 9
  • 33
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
  • 385
  • 2
  • 9
2
votes
1 answer

Couchbase 4.5 - Index is not covered when array is used in where clause

I have a Couchbase(4.5) bucket my-data. A minimal overview of the bucket is as follows. Document structure { _class: “com.dom.Activity”, depId: 984, dayIds: [17896, 17897, 17898], startTime: 10, endTime: 20 } Index I also have…
AJA
  • 456
  • 3
  • 12
2
votes
2 answers

How can a covering index satisfy more than one query?

I've inherited a MS Sql database hosted in Azure. Looking for performance improvement, I've been reading a lot about indexing and covering index. (Maybe this is the most complete reading that I've found:…
Felipe Ramalho
  • 47
  • 5
2
votes
2 answers

Proper field orders for covering index - MySQL

Is there a standard order to create a covering index for a table in MySQL? Meaning if I have query that has a where clause, order by and the fields in the select statement, in what order would I have the fields to the index to properly create a…
John
  • 9,840
  • 26
  • 91
  • 137
2
votes
2 answers

Why does the following query copy table data?

SELECT COUNT(*) AS cnt FROM products WHERE ExternalProductId IS NOT NULL GROUP BY SourceId, ExternalProductId HAVING cnt > 1 There is an index on (ExternalProductId, SourceId, AnotherField). An Explain shows the index is used. This is printed in…
ripper234
  • 222,824
  • 274
  • 634
  • 905
2
votes
1 answer

Covering Index and two tables in mysql

I've got two tables (NewProducts and OldProducts) that are being compared. NewProducts has about 68,000 records and OldProducts about 51,000. I'm using a covering index on each table, however the query is taking 20 minutes to execute, so I'm not…
user3314053
  • 239
  • 1
  • 3
  • 11
2
votes
1 answer

Optimizing usage of covering indexes

I had never heard of covering indexes before and just came across them. I was reading this page on them and it says.. "A covering index can dramatically speed up data retrieval but may itself be large due to the additional keys, which slow down data…
Brett
  • 19,449
  • 54
  • 157
  • 290
1
vote
4 answers

Simple MySQL indexing issue

I ve got this table: CREATE TABLE IF NOT EXISTS `test1_nopart` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idAccount` int(10) unsigned NOT NULL, `data` mediumint(8) unsigned NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), KEY…
nemenems
  • 1,064
  • 2
  • 9
  • 27
1
vote
2 answers

SQL Server 2008 Index Optimization - clustered lookup vs nonclustered include

This is a long, involved question about index optimization theory. This is not homework, though I was first exposed to this question in a sample exam for Microsoft's 70-432. The original question was about general query optimization, but then I…
Wanna-be-guru
  • 13
  • 2
1
vote
1 answer

Composite Index and Covering Index in SQL Server

I have a table with (ID, col1, col2) & query of SELECT ID FROM table WHERE col1 = 2 AND col2 = 7 Should I create a composite index CREATE NONCLUSTERED INDEX IDX_Composite ON table (ID, col1, col2) or covering index CREATE NONCLUSTERED INDEX…
skye
  • 23
  • 3
1
vote
1 answer

Can MySQL satisfy a query from a prefix index?

I wonder if a covering index can help, for some rows, satisfy a query from a LONGTEXT or any other LOB column? (MySQL 8, MariaDB 10.5) I have this table (WordPress-defined): CREATE TABLE wp_options ( option_id BIGINT UNSIGNED NOT NULL…
O. Jones
  • 103,626
  • 17
  • 118
  • 172
1
vote
1 answer

Materialized view only for covering index

Given the following table car_year of car model's year: | id | maker | model | year | ---------------------------------- | 6 | Audi | Allroad | 2001 | | 12 | Audi | A8 | 2008 | | 14 | Ford | Mustang | 1996 | | 15 | Honda | …
ElBidoule
  • 169
  • 1
  • 2
  • 9
1
vote
2 answers

What is a MySQL covering index?

I saw the documentation describing covering index: covering index An index that includes all the columns retrieved by a query. Does it mean that the covering index is a specific index? I think covering index is a phenomenon. If i follow the…
Zed
  • 23
  • 1
  • 7
1
2
3 4