Indexes with multiple columns in the same one index.
For questions about indexing a database by multiple columns at once.
Questions tagged [compound-index]
116 questions
1
vote
1 answer
MongoDB seemingly not honoring uniqueness contraint on compound index
I have a collection with approximately 500 million documents where it seems the uniqueness constraint has not been enforced on a specific subset of these. The uniqueness contraint applies to a compound index.
The indices on this…
Dr DR
- 667
- 1
- 5
- 8
1
vote
0 answers
How to improve query performance by two and more array fields in MongoDB?
I have collection of objects which have multiple array fields. Something like:
{
sponsors: {
lead_sponsor: {
name: "Mark",
type: "Individual"
}
collaborators: [
{name: "Company Ltd.", type:…
Rayz
- 523
- 1
- 4
- 19
1
vote
2 answers
Mongodb unique compound index not honoring Dates
I'm having trouble with a unique compound index in mongodb. Code speaks best so (in mongo shell):
var collection = db.getCollection('test');
collection.drop(); // start from scratch
collection.createIndex({date:1});
collection.createIndex({_id:1,…
Josh Kim
- 215
- 4
- 14
1
vote
1 answer
How to find documents having a query-value within the range of two key-values
I'm analyzing texts. Those texts have annotations (e.g. "chapter", "scenery", ...). Those annotations are in my MongoDB collection annotations, e.g.
{
start: 1,
stop: 10000,
type: chapter,
details: {
number: 1,
title: "Where it all…
R_User
- 10,682
- 25
- 79
- 120
1
vote
2 answers
multiple constraint keys
if i have tabled:
Resource (id (PK), name)
Manager (id(PK), resource_id (FK), manager_resource_ID(FK))
Should resource_id and manager_id both be foreign keys into the Resource table.
i obviously dont want to enter any values in each of those…
leora
- 188,729
- 360
- 878
- 1,366
1
vote
1 answer
mysql do compound indices index individual fields as well?
I have the following MySQL/InnoDB table. I added a compound index as the primary key on both columns, and I have also added two single column indexes. With the compound index in place am I getting any performance increase from the single column…
Duncan
- 443
- 1
- 5
- 12
0
votes
4 answers
SQL select clause with a compound
I have two lookup/reference tables (Subscribed and Unsubscribed) in my Microsoft SQL Server 2008 database with the following structure:
UserId int
PublicationId int
These fields are indexed together as a compound index.
What I want to be able to do…
Neilski
- 4,385
- 5
- 41
- 74
0
votes
1 answer
MongoDB, how to create index for query containing a geospatial query, a range query and sort on different columns?
So if I have a query that does the following (in pseudo code)
find(a nearby x, b > y).sort(c)
where a is a geo column, b is type of long, and c is also a type of long
Would the compound index on (a:2d, b:1, c:1) work and suggested?
tom
- 14,273
- 19
- 65
- 124
0
votes
1 answer
Create Multiple Index Or Create one Compound Index on MongoDb Collection
I got a Collection with following structre
_id : 641c2844904d1c6a6e8985a6
PortalId:123,
ContactId :1,
SelectionId:1,
SelectionName:"TEST",
SelectionCategory:"cat",
SelectionCategoryId:1,
DateCreated:2023-03-23T10:21:55.578+00:00
CreatedBy: "test"
I…
Sebastian
- 4,625
- 17
- 76
- 145
0
votes
0 answers
How does Elasticsearch/Lucene achieve such performance when querying multiple fields?
According to the answer given here, Elasticsearch doesn't seem to use compound indexes for querying multiple fields, and instead queries multiple indexes and then intersects the results.
My question is how does it achieve such high performance?…
whit3.oc7opus
- 37
- 5
0
votes
1 answer
Mongo Db ESR rule for multiple index keys in a compound index
Collection: appointments
Schema:
{
_id: ObjectId();
userId: string;
calType: string;
status: string;
appointment_start_date_time: string; //UTC ISO string
appointment_end_date_time: string; //UTC ISO string
}
Example:
{
…
codeinprogress
- 3,193
- 7
- 43
- 69
0
votes
1 answer
How to query records in mongoose with schema having compound index with normal field and referenced field?
I have a Student Schema defined using mongoose. To create a primary key with both name and subject field, i have created a compound index with name and subject field.
@Schema()
export class Student {
@Prop({ type: String, required: true, unique:…
Ashwani Sharma
- 1
- 2
0
votes
1 answer
mongodb - Multiple Compound Indexes involving a common field
We have a collection with millions of data. This data is being rendered in the UI for stats purpose and hence time to render is of key importance.
The queries to render the data involve the below fields:
field_a and field_t
field_b and…
sid_bond
- 135
- 3
- 11
0
votes
4 answers
MariaDB 5.5.68, prevent toxic selects?
I know that this MariaDB version 5.5.68 is really out of date, but I have to stay with this old version for a while.
Is there a way to prevent toxic selects, possibly blocking MyISAM tables for a longer time (minutes)? The thing is that the select…
Volker
- 428
- 4
- 15
0
votes
1 answer
What is the mongo compound index keyword arrangement rule? Numbers first, strings later?
When I used numbers as keys, the compound index prefixes created were not as expected。
To define a compound index:
admin> use TestDB
TestDB> db.createCollection('coll')
TestDB> db.coll.createIndex({4:1,1:-1},{unique:true})
TestDB>…
intellectual
- 1
- 1