Stack Overflow
Stack Overflow

Questions tagged [compound-index]

93 questions
39
votes
2 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
  • 10,080
  • 11
  • 64
  • 94
19
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
  • 10,275
  • 24
  • 74
  • 121
14
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
  • 163
  • 1
  • 6
9
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
  • 81,986
  • 49
  • 214
  • 348
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,245
  • 4
  • 17
  • 27
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
  • 128
  • 7
5
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
  • 51
  • 5
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
  • 667
  • 14
  • 30
4
votes
2 answers

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
  • 1,618
  • 1
  • 24
  • 36
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,258
  • 2
  • 21
  • 36
4
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
  • 9,920
  • 18
  • 48
  • 71
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
  • 63
  • 2
  • 7
3
votes
1 answer

Unique compound index on array fields

I am trying to create mongo document with compound index. My sample doc looks like this { fname: "fname1", lname : "lname1", task : ["t11", "t12", "t13"] } { fname: "fname2", lname : "lname2", task : ["t21", "t22",…
Ramesh Papaganti
  • 5,733
  • 3
  • 22
  • 33
3
votes
1 answer

compound index syntax in mongodb

a quick question regarding how to define a compound index as per the definition I can create a compound index as below and it would create a compound index for student_id and…
user641887
  • 1,350
  • 2
  • 26
  • 46
1
2 3 4 5 6 7