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
3
votes
1 answer

Creating indexing for dot annotated dynamic path in firestore [Wildcard]?

I have my collection created as below: -products -productID -category [object] catitem1 - boolean catitem2 - boolean now I have written a query as below this.afs.collection('products', ref => ref …
Guruprasad J Rao
  • 29,410
  • 14
  • 101
  • 200
3
votes
1 answer

Can DataMapper properties appear in multiple composite indexes?

I found that this issue had been discussed in Ticket #58 of DataMapper, apparently way back in 2007, but I can't find how to do it in the latest version (dm-core-0.10.2). I want to define two composite indexes, each of which are partially based on a…
Chris
  • 632
  • 4
  • 11
  • 18
3
votes
2 answers

MySQL Innodb: Large Composite PK no other indexes

I am creating an Innodb table with four columns. Table column_a (tiny_int) column_b (medium_int) column_c (timestamp) column_d (medium_int) Primary Key -> column_a, column_b, column_c From a logical standpoint, columns A, B, C must be made…
ProfileTwist
  • 1,524
  • 1
  • 13
  • 18
2
votes
3 answers

Composite index on three columns

We have got 93M rows mapping table which is keeping mapping information of three tables and corresponding three tables. We are facing performance issues in accessing data from the mapping table. TableName PK information Number of…
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
2
votes
1 answer

What is the correct order for composite indexes (mysql)

I've read that in composite indexes, you should order the columns with the most specific first, however my thought is that the most optimal route would be for least specific indexes to be covered first due to my understanding (or lack there of) on…
gitbugr
  • 21
  • 1
2
votes
1 answer

MySQL 8 slow with descending composite index

I have the following query: SELECT shows.id, shows.title, shows.thumbnail, shows.views, shows.likes, shows.dislikes, shows.duration, shows.hd, shows.origin, …
Even Steven
  • 137
  • 6
2
votes
1 answer

Provide text column length on composite unique index creation

I'm trying to create an index on a new table that requires uniqueness on the agency_id (integer) and the IP address (text). I know that I need to provide a length on the index for IP. But I'm having issues assigning the length to just the IP column.…
WhoaItsAFactorial
  • 3,538
  • 4
  • 28
  • 45
2
votes
3 answers

How to index collection by using composite key

I have this class public class Item { public int UniqueKey; public int Key1; public int Key2; public int Key3; public int Key4; public string Value; } and the collection IEnumerable I want to create indexes on items…
garik
  • 5,669
  • 5
  • 30
  • 42
2
votes
1 answer

Why does Postgresql not use Index Only Scan on my composite index in this case?

My table has integer columns "a", "b". There are only few (<30) different values for "a", but for each "a", huge number of different "b" exists (>10**7). To speed things up, I created composite index (a,b). I observe that select count(*) from tab…
Tomas Kulich
  • 14,388
  • 4
  • 30
  • 35
2
votes
1 answer

EF Migrations drops index when adding compsite index

I noticed EF removed an index on a foreign key when I added a composite index with the foreign key. So I need to understand composite indexes better :) I added the composite index using this answer and generated my EF code first migration…
Snæbjørn
  • 10,322
  • 14
  • 65
  • 124
2
votes
1 answer

Index on Composite attributes

When we create an index on an attribute a tree is created for this attribute. But what happens when we create an index with composite attributes? Two trees are created? Both are part of the same tree? What?
Cratylus
  • 52,998
  • 69
  • 209
  • 339
2
votes
1 answer

Do composite indexes in MySql work both ways round?

Consider a MySql table having the following schema +-------------------+------------+------+-----+- | Field | Type | Null | Key | +-------------------+------------+------+-----+- | id | int(11) | NO | PRI | |…
Arindam
  • 998
  • 1
  • 8
  • 20
1
vote
1 answer

Is a composite index row created for an entity if one of the properties is missing?

Is a composite index row created for an entity if one of the properties is missing ? If half of the entities are missing one property will the index table be only half the size ?
Mahron
  • 21
  • 4
1
vote
1 answer

Why would you have a clustered composite index when none of the fields are used together?

In a legacy database (SQL Server 2000), we have a clustered index that looks like this: CREATE CLUSTERED INDEX [IX_usr] ON [dbo].[usr] ( [uid] ASC, [ssn] ASC, [lname] ASC ) The thing is, as far as I know none of these fields are used…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
1
vote
3 answers

is a db index composite by default?

when I create an index on a db2, for example with the following code: CREATE INDEX T_IDX ON T( A, B) is it a composite index? if not: how can I then create a composite index? if yes: in order to have two different index should I create them…
lucaconlaq
  • 1,511
  • 4
  • 19
  • 36
1
2
3 4 5 6