Questions tagged [index]
A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns sorted but structures the data differently to allow faster access.
3,415 questions
0
votes
0
answers
25
views
MongoDB compound index usage to avoid in-memory sorting
This question on MongoDB compound index:
Below is a sample document in a given collection test.
{ a : 5, b : 3, c: 2, d : 1 }
Given a compound index { a: 1, b:1, c:1, d:1}, Which of the below query ...
- 1,019
0
votes
0
answers
61
views
Removing Text Columns to Alleviate Indexing Issues
I have a couple of tables in a database which I inherited which have NVARCHAR(MAX) columns.
Now that the data is several million rows, adding an index takes a long time and because of those columns, I ...
- 227
1
vote
1
answer
108
views
Non clustered index including all columns smaller than the clustered index
While messing around in SQL Server to get a better grasp on indices, I'm quite surprised to see that if I create an index (on another column than the PK) that includes all columns, it is still lighter ...
- 227
0
votes
0
answers
22
views
GridDB Cloud: is CREATE INDEX on a TimeSeries container an online (non-blocking) operation?
I run GridDB on GridDB Cloud (Free plan) with a single TimeSeries container that’s actively ingesting data. I need to add a secondary index on deviceid to speed up point/range lookups.
Environment:
...
- 111
0
votes
1
answer
45
views
Does MongoDB's ESR (Equality, Sort, Range) guideline apply to SQL databases too?
MongoDB has an Equality, Sort, Range (ESR) guideline that has helped me tune my MongoDB indices in the past.
Now I'm using a SQLite database on a side-project of mine and I'm curious if ESR guidelines ...
- 103
1
vote
0
answers
30
views
Do I need a secondary index on a non–row-key column for this query, and how do I create/verify it?
I’m testing GridDB Cloud with a 3-column Collection container:
field
type
sensor_id
STRING row key
temperature
FLOAT
recorded_at
TIMESTAMP
I insert a row and then query by a non–row-key column:
...
- 11
3
votes
1
answer
122
views
Inoptimal Fill Factor on objects in sys schema
I have run the legendary sp_blitz on one of my servers, which has thrown up a warning about some fill factors that have been changed.
When I run the query from brentozar.com/go/fillfactor which ...
- 2,264
0
votes
3
answers
90
views
Correct way to index a boolean column when used with other columns
When implementing soft delete on a table that can be searched by other columns, which is the correct way to index it?
Let's say the table has an id field, and a couple of text fields, and finally a ...
- 101
0
votes
1
answer
52
views
What happens in MariaDB if an index is dropped from a table while a join is running on that table?
I am running Fedora 42 KDE spin, and the output from mysql -V is
mysql Ver 15.1 Distrib 10.11.11-MariaDB, for Linux (x86_64) using
EditLine wrapper
I have a script that runs weekly that creates a ...
- 161
0
votes
1
answer
74
views
MariaDB uses a unique index on one server, but not others
What makes MariaDB (10.6.22) use a unique index in queries on one server (production) but not on others (staging and dev, both of which are based on the production dumps).
Namely I have a table ...
- 101
1
vote
0
answers
41
views
Necessary to include filtered index filter column if value always NULL in PostgreSQL?
My question is about PostgreSQL. I found similar questions for MS SQL server but I don't know if the answers apply here.
My table looks like this:
scores
======
| ID | UserID | ValidFrom | ValidUntil ...
- 181
2
votes
0
answers
29
views
How can I use multicolumn indexes to efficiently find the most recent record for EACH unique id (Postgres)? [duplicate]
I have a large temporal table (>20 million rows) that contains data over time on about 15k unique objects. I can't figure out how to efficiently query "get the most recent record for each ...
- 21
1
vote
1
answer
51
views
working of index in join query
My question is regarding the Explain output. Please refer the below query and its Explain command output
Explain
update
test.table1 t1
join test.table2 t2
on t1.field1=t2.field1
and t1.field2=t2....
- 209
0
votes
2
answers
99
views
Is it reasonable to use indexes that cover more than two columns?
Suppose I have a table file with four columns name, uploaded_at, size and mime_type.
I want the user to be able to sort by these columns in any order and direction (ASC/DESC). So, as far as I know, I ...
- 179
1
vote
2
answers
277
views
Efficient approach for filtering, paging and sorting across multiple tables with multiple key columns
I have a query that retrieves data from a complex data structure involving multiple tables. The data can be filtered using a tree component that allows selection of different keys from different ...
- 19