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,416 questions
0
votes
2
answers
13
views
Are leaf nodes on an InnoDB secondary index sorted by both secondary and primary key columns?
For example, if a table has columns A, B, ID, and (ID) is the primary key, and (A) is a secondary index.
I wonder if the leaf nodes on the secondary index (A) is sorted by (A,ID), or just (A), and the ...
- 101
3
votes
1
answer
78
views
What is the maintenance on the index caused by insert, update, or delete operations?
Follow up question for this answer regarding the excerpt of the sys.dm_db_index_usage_stats doc:
The user_updates column is a counter of maintenance on the index
caused by insert, update, or delete ...
- 6,511
0
votes
0
answers
32
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
66
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
112
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
23
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
51
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
32
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
126
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,256
0
votes
3
answers
100
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
54
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
44
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
59
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