Stack Overflow
Stack Overflow

Questions tagged [composite-index]

Composite index: An index that contains more than one column.

A composite index is useful when there are multiple fields in the WHERE clause of an SQL statement and all fields combined will give significantly less rows than the first WHERE clause field.

81 questions
160
votes
9 answers

When should I use a composite index?

When should I use a composite index in a database? What are the performance ramification by using a composite index)? Why should I use use a composite index? For example, I have a homes table: CREATE TABLE IF NOT EXISTS `homes` ( `home_id`…
Teddy
  • 1,601
  • 2
  • 11
  • 5
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
25
votes
1 answer

Is there an optimal method for ordering a MySQL composite index?

I am looking into adding a composite index to a table in a MySQL database which will likely be several million rows in size. The composite will be comprised of two varchar columns as well as three int columns. My question is as stated in the title:…
chicagoCrazy
  • 374
  • 1
  • 4
  • 12
13
votes
1 answer

Composite Clustered Index in SQL Server

I have a table with a IDENTITY Column as Primary Key (a classic ID column). SQL Server create automatically a Clustered Index for that Primary Key. My question is: Can I have a only single CLUSTERED INDEX composite with more columns? If yes, how…
GibboK
  • 71,848
  • 143
  • 435
  • 658
12
votes
5 answers

Difference between 2 indexes with columns defined in reverse order

Are there any differences between following two indexes? IDX_IndexTables_1 IDX_IndexTables_2 If there are any, what are the differences? create table IndexTables ( id int identity(1, 1) primary key, val1 nvarchar(100), val2…
dance2die
  • 35,807
  • 39
  • 131
  • 194
9
votes
5 answers

Oracle composite index for range query conditions

I had a table Blah ( latitude float, longitude float, create_time date, owner_id int , ..... ) and my code does only a single query select * from Blah where latitude < l1 and latitude > l2 and longitude < ll1 and longitude > ll2 and…
teddy teddy
  • 3,025
  • 6
  • 31
  • 48
8
votes
1 answer

Overhead of Composite Indexes

I have many tables where I have indexes on foreign keys, and clustered indexes which include those foreign keys. For example, I have a table like the following: TABLE: Item ------------------------ id PRIMARY KEY owner FOREIGN…
Kirk Backus
  • 4,776
  • 4
  • 32
  • 52
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
6
votes
2 answers

Separate Join clause in a Composite Index

Would having a Composite Index be beneficial for something like this: SELECT * FROM a INNER JOIN b ON(a.id=b.id) INNER JOIN c ON(a.bar=c.id) INNER JOIN d ON(a.foo=d.id) Index would be: (a.id, a.bar, a.foo)
user1789469
  • 105
  • 6
5
votes
2 answers

Do mysql composite indexes make some other indexes completely redundant?

If I have an a composite index on (a, b) I understand that queries only concerned with 'a' will still use the composite index (but not queries concerned with 'b') My question is whether there is any valid reason to have a single-column index on 'a'…
Brian Deacon
  • 21,384
  • 13
  • 39
  • 41
4
votes
1 answer

Does indexing on a part of a composite primary key is needed in Postgresql?

I have a (large) table with a composite primary key, composed of 5 columns (a, b, c, d, e). I'd like to efficiently select all rows having two of those columns (a + e) to a given value. In PostgreSQL, do I need an index for this? Or will the…
Laurent Grégoire
  • 4,006
  • 29
  • 52
4
votes
2 answers

Can MySQL use a composite index where one of the fields is WHERE field > 0?

We use composite indexes in many places and they work great. Our platform has a relatively complex "report builder" that dynamically writes queries based on what the users select. We choose indexes based on common queries and often our composite…
yyyyyyyyyyyyyyyyy
  • 115
  • 6
4
votes
1 answer

Composite Index - SQL Server

The sys.dm_db_index_usage_stats DMV gives information about number of seeks and scans against a index in sql server I have a large number of composite index that includes multiple columns. I suspect that these indexes cause a lot of maintenance…
Devanathan A
  • 53
  • 6
4
votes
3 answers

Which Composite Index would make this simple MySQL query faster?

Which Composite Index would make this simple MySQL query faster, and how would I create that Composite Index? SELECT * FROM `Table1` WHERE `col1` = '145307' AND `col2` = '0' AND col3 NOT IN ( 130209, 130839 ) ORDER BY col4 DESC LIMIT 0 ,…
ProgrammerGirl
  • 3,157
  • 7
  • 45
  • 82
4
votes
1 answer

Advice on composite index

I have a huge (millions of rows) table that contains the following columns: [When] datetime2(0), [What] tinyint, [Who] bigint It's basically a table of events ('What') that happened to various entities ('Who') at a time ('When'). There are about 10…
Barguast
  • 5,926
  • 9
  • 43
  • 73
1
2 3 4 5 6