Stack Overflow
Stack Overflow

Questions tagged [compound-index]

Indexes with multiple columns in the same one index.

For questions about indexing a database by multiple columns at once.

116 questions
50
votes
3 answers

How does the order of compound indexes matter in MongoDB performance-wise?

We need to create a compound index in the same order as the parameters are being queried. Does this order matter performance-wise at all? Imagine we have a collection of all humans on earth with an index on sex (99.9% of the time "male" or "female",…
Redsandro
  • 11,060
  • 13
  • 76
  • 106
21
votes
2 answers

MySQL: make a compound index of 3 fields, or make 3 separate indices?

I have a MySQL table that has, among other attributes, a timestamp, a type and a user_id. All of them are searchable and/or sortable. Is it better to create an index for each one, or create a single compound index with all three, or both?
Petruza
  • 11,744
  • 25
  • 84
  • 136
15
votes
4 answers

creating a compound index in c#

I want to create a compound index where one key should be in ascending, the second key in descending order. How can I do this? I have a string containing the property names the user…
user2010435
  • 173
  • 1
  • 1
  • 6
11
votes
3 answers

Will SQL Server use a compound index when only a single column is in the WHERE clause?

Say I've got a table: CREATE TABLE Users ( Id INT IDENTITY (1, 1), FirstName VARCHAR(40), LastName VARCHAR(40) ) Queries are usually on FirstName or LastName, but also on FirstName and LastName. If I create a non-clustered index on…
Roger Lipscombe
  • 89,048
  • 55
  • 235
  • 380
8
votes
2 answers

Searching for compound indexes in IndexedDB

After reading here for ages, I've finally registered to ask a question. I've been messing around with IndexedDB lately and stumbled over a problem with compound indexes (I use them somilar to the example here). I have an object in the objectstore…
steve-o-mat
  • 81
  • 1
  • 5
6
votes
1 answer

MongoError: E11000 duplicate key error collection for unique compound index

Question is related to unique compound index unlike other such questions which have unique index only. I also have sparse: true for the indexes. I've the following indexes in my collection [ { "v": 2, "key": { "_id": 1 }, …
abhisekp
  • 4,648
  • 2
  • 30
  • 37
6
votes
2 answers

Compound index with three keys, what happens if I query skipping the middle one?

With PostgreSQL, I want to use a compound index on three columns A, B, C. B is the created_at datetime, and occasionally I might query without B. What happens if I compound index on (A, B, C) but then query with conditions on A and C, but not B?…
Some Guy
  • 12,768
  • 22
  • 58
  • 86
5
votes
3 answers

mysql multi column index not working (as expected)?

I have a table like this CREATE TABLE IF NOT EXISTS `tbl_folder` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_userid` int(11) NOT NULL, `name` varchar(63) NOT NULL, `description` text NOT NULL, `visibility` tinyint(4) NOT NULL DEFAULT…
alp_chen
  • 53
  • 2
5
votes
6 answers

Optimizing a SQL query to avoid full table scan

Consider the following query: SELECT * FROM Transactions WHERE day(Stamp - interval 3 hour) = 1; The Stamp column in the Transactions table is a TIMESTAMP and there is an index on it. How could I change this query so it avoids full table scans?…
emx
  • 1,295
  • 4
  • 17
  • 28
5
votes
1 answer

Indexing methods in parse for _rperm

In trying to understand how Parse indexes work with Mongo, I noticed that some of my slow queries were using the _rperm field (from the ACL) even though I didn't specify it in the indexes that I defined. It isn't on every query either so I'm…
Tyler Slater
  • 127
  • 8
5
votes
2 answers

MongoDB compound index usage

Lets say I have document with the following two keys: 1) key1 2) key2 If I am creating compound index on both of them.. {'key1':1,'key2':1} When running a query relevant only for key1.. does the index above is used? or I need to create specific…
assaf_miz84
  • 687
  • 2
  • 15
  • 33
4
votes
4 answers

multicolumn index column order

I've be told and read it everywhere (but no one dared to explain why) that when composing an index on multiple columns I should put the most selective column first, for performance reasons. Why is that? Is it a myth?
milan
  • 2,355
  • 2
  • 23
  • 38
4
votes
1 answer

IndexedDB IDBKeyRange Compound/Multiple Index not working

Can anyone explain why my IDBKeyRange only seems to be filtering on the first column? I have an index defined as follows: osDrugs.createIndex("combined", ["name", "strength", "form", "packsize"], {unique: false}); My query is roughly as…
Daniel
  • 73
  • 2
  • 7
3
votes
0 answers

MongoDB Compound Index Find then Sort Aggregation Efficiency

I have a large collection ~20m-30m documents that are indexed, but my aggregation performance is keeping up with the growing size. (Using DocumentDB which uses ~MongoDB 4.0). Basic schema (there are a number of other fields, but these are the…
canpan14
  • 1,181
  • 1
  • 14
  • 36
3
votes
0 answers

Indexeddb compound index IDBKeyRange.bound with nested arrays

I have an indexeddb keypath with arrays like this: ... [0,1,3] [0,1,4] [0,1,5] ... and I can get them by this: IDBKeyRange.bound([0,1,2], [0,1,5] ,false,false) The problem is when I compound the index to this…
ccsacsa
  • 31
  • 2
1
2 3 4 5 6 7 8