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 is ignored when selecting unindexed columns (Oracle)

We have a simple join statement in which some of the when clauses may turn into is null. The statement is generated by an application. A problem with a query plan arises when we have this is null constraint. We followed the approach described in…
Zaplatki
  • 35
  • 7
1
vote
1 answer

cassandra composite index and compact storages

I am new in cassandra, have not run it yet, but my business logic requires to create such table. CREATE TABLE Index( user_id uuid, keyword text, score text, fID int, PRIMARY KEY (user_id, keyword, score); ) WITH CLUSTERING ORDER BY…
user3201666
  • 33
  • 4
1
vote
2 answers

Composite index

I am new to MySQL, and I need to add indexes on an existing table (which contains roughly 200K rows). Table mytable: (id:integer, created_time:timestamp, deleted_time:timestamp) I have 2 queries which need to benefit from the index: select s.id…
kiv
  • 1,595
  • 1
  • 9
  • 11
1
vote
1 answer

Confused about mysql looking at half a million rows when using an index

I have a table (location_data) with 300M rows (and growing). I want to find the id of the latest entry (MAX(id)) for a particular value of foo_id. Is the query below optimal? It often takes a few seconds. Does the "rows" value of 561826 mean that…
fadedbee
  • 42,671
  • 44
  • 178
  • 308
1
vote
2 answers

How to avoid a filesort on a mysql composite index for a prefix query with an order by statement?

I have a 1 GB mysql table with three colums (german bigrams): create table sortedindex (source varchar(60),target varchar(60),score float) engine=myisam character set utf8 collate utf8_bin; I also created a composite index: create index…
Rene Pickhardt
  • 692
  • 5
  • 17
0
votes
1 answer

NHibernate Composite Index, not a key

Still a n00b in NHibernate :( I want to add a composite index to the hbm.xml of one of my POCOs, for performance purposes. It does not relate directly to a class, but rather two common values I will be querying against. I think I need to do a…
Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
0
votes
1 answer

What is a MYSQL composite index?

Advantages? and why not just index fields separately? can any body explain me in simple terms.
Abhimanyu
  • 4,752
  • 7
  • 33
  • 44
0
votes
2 answers

When index merging is possible with Firestore

I am trying to enable a variety of queries in an app using Firestore, and I recently had to add several composite indexes to allow them to work. But I am noticing a lot of overlap in the queries and wondering if it's possible to merge some of them…
joeymigs
  • 59
  • 7
0
votes
1 answer

Confused about mysql compsite index and leftmost prefix matching

My MySQL version is 5.7 and I created a test table with 1,332,660 records: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data_name` varchar(500) DEFAULT NULL, `data_time` varchar(100) DEFAULT NULL, `data_value` decimal(50,8) DEFAULT…
lant
  • 391
  • 2
  • 10
0
votes
0 answers

Optimize query with composite index

"I'm having trouble with a query that uses a composite index. When I use a regular index, the query takes about 1 second to execute on a table with 3 million rows. However, when I add a composite index, the query only improves by about 0.982 seconds…
Mộc Uyển Thanh
  • 67
  • 1
  • 4
0
votes
0 answers

Opinions on a Weekly Availability System for Firestore

I am trying to build a data model for weekly service availability of users in the Firebase Firestore to store and query the data efficiently. Is there any known alternative method for Firestore for this kind of requirement? How can I improve this…
Abdulmelik Saylan
  • 44
  • 5
0
votes
1 answer

Postgres: Sort Unique Index by a separate column

Consider a many-to-many relationship of books in a bookshelf: CREATE TABLE bookshelf_books( bookshelf_id text NOT NULL, book_id text NOT NULL, ordering text NOT NULL ); CREATE UNIQUE INDEX bookshelf_book ON bookshelf_books(bookshelf_id,…
Stepan Parunashvili
  • 2,627
  • 5
  • 30
  • 51
0
votes
1 answer

Cannot assemble COIN object, due to column names and indicator codes not matching with makeCOIN(IndData) in COINr6 R package

This question concerns the package COINr6 for R, used for composite indicator construction. I am encountering a problem when assembling my COIN. The error when calling KULI <- assemble(IndData = data, IndMeta = metadata, AggMeta = aggmeta) is: Error…
mankojag
  • 61
  • 5
0
votes
1 answer

Flutter Firestore Composite Index with variable item not realistic?

I just created a StreamBuilder that has this stream: stream: db .collection('GearLockerItems') .where('inPack.$packID', isEqualTo: true) .orderBy('itemName') .snapshots(), When I ran the code, it didn't…
Ten Digit Grid
  • 1,315
  • 4
  • 22
  • 43
0
votes
1 answer

How to show composite index in mysql?

I'm writing a WordPress plugin, and I want to check to make sure a composite index is placed on the postmeta.post_id and postmeta.meta_key columns together when the plugin is being activated and to warn the user to create this index if it has not…
kloddant
  • 1,026
  • 12
  • 19
1 2 3
4
5 6