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
1
vote
1 answer

Composite index for Cosmos db not improving RU usage

Azure notified me that a query I had was not performant because of an ORDER BY clause and it would greatly benefit from adding a composite index. I've done a bunch of reading and tried repeatedly to add a composite index for this query but…
Brandon Parker
  • 762
  • 7
  • 18
1
vote
0 answers

Is it possible to create a composite index using a struct and a field inside another sruct?

I was wondering if it would be possible to create an index using a primitive field and the fields of another struct inside a third struct. I have a YearMonth struct that has some convenient methods I use across my API I also use this struct to…
Rafael Cruz
  • 11
  • 1
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

How to create composite indexes in datastore to filter with multiple attributes in entity

We are using Google Datastore for our dashboard. In dashboard we provide filtering option for endusers. Lets say we have an datastore kind whose structure is: { 'attribute1': 'val1', 'attribute2': 'val2', 'attribute3': 'val3', …
Sri Harsha
  • 641
  • 2
  • 8
  • 18
1
vote
2 answers

Optimise Mysql Query

I would like to optimise the query I have below: SELECT a.id, a.pay_point_name, a.pay_point_location, COUNT(b.id) AS countedCustomers, SUM(b.approved) AS summedLoans, …
Peter
  • 60
  • 8
1
vote
1 answer

Define a composite index in EdgeDB

How do you define a composite index in EdgeDB? The documentation states: The simplest form of index is an index, which references one or more properties directly: type User { property name -> str; index on (__subject__.name); } but I…
CodesInChaos
  • 106,488
  • 23
  • 218
  • 262
1
vote
2 answers

Composite index for optional field in Cosmos

I have a collection in Cosmos DB which contains documents of different types (and schemas): { "partKey": "...", "type": "type1", "data": { "field1": 123, "field2": "sdfsdf" } } { "partKey": "...", "type": "type2", "data": { "field3":…
dr11
  • 5,166
  • 11
  • 35
  • 77
1
vote
1 answer

JDBC: Unable to get composite index for my table

I am trying to list all the indexes for all the tables in my DB (using a generic solution which is not specific to an RDBMS) To test out my solution, I have 2 tables in an MS SQL Server DB tempdb Stores Staffs Each table has a PK and the staffs…
Syed Mauze Rehan
  • 1,125
  • 14
  • 31
1
vote
0 answers

AWS RDS restored from snapshot is super slow with the composite index

So I have 2 tables, each of them is 25GB. Table t1 has an index on a single column and t2 has a composite index. All my queries are simple query making use of the index. my t2 table CREATE TABLE `t2` ( `uid` bigint(20) DEFAULT NULL, `time`…
wwwwan
  • 407
  • 1
  • 4
  • 12
1
vote
1 answer

Why my MySQL composite index has less Cardinality than a single index on the same column?

I first created 2 separate indexes in my table: uid and time. Then I decided to create a composite index(uid, time). But why the Cardinality of uid in a composite index(row 3) is less than the Cardinality of uid in a single index(row 1)? mysql>…
wwwwan
  • 407
  • 1
  • 4
  • 12
1
vote
2 answers

Concurrent queries on composite index with order by id drastically slow

I have a table defined as follows: | book | CREATE TABLE `book` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `provider_id` int(10) unsigned DEFAULT '0', `source_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `title` varchar(255)…
seaguest
  • 2,510
  • 5
  • 27
  • 45
1
vote
0 answers

Laravel Composite Index Issue

I checked this solution. #1071 - Specified key was too long; max key length is 1000 bytes But I want to know how to do this in laravel migration file as I can't do it directly in mysql, I need a solution which can change the length of the index…
Imran Farooq
  • 115
  • 2
  • 10
1
vote
1 answer

Google Cloud Datastore : Composite Index : Order by does not seems to work

I am using Google Cloud datastore with (C#) .NET Code, I have table called Audit in Datastore with following columns ID/Name (long), ID (long), ActionType (String), GroupType (String), DateTime (TimeStamp), CompanyID (long), UserID (long), ObjectID…
Hardik Patel
  • 123
  • 10
1
vote
1 answer

How to use composite index in range query in mysql

I have a table "test" with two field, field1 and field2, and an composite index created field1_field2(field1, field2). here is a sql : select * from test force index(field1_field2) where field1 > 100 and field2 = 2 limit 200 and it doesnt run very…
Mr rain
  • 983
  • 5
  • 13
  • 27
1
vote
2 answers

InnoDB composite indexing order and INSERT performance

I have a MySQL table with 3 columns on which I'd like to use a multi-column index. Column A is TINYINT, B is SMALLINT and C is VARBINARY (16). Should I use the index as A, B, C, because A has lower granularity than B and B lower than C to achieve…
pedmillon
  • 143
  • 3
  • 13
1 2
3
4 5 6