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,271
questions
0
votes
1
answer
97
views
How to speed up SELECT from not-unique column?
I have a simple query in a Postgres 15 database. No joins or other fancy stuff:
select * from my_table where col='val'
The goal is to speed up the query. I'd say I need an index, but the values of the ...
- 285
0
votes
3
answers
86
views
Increased LIMIT results in performance degradation
This appears to be a variation of a common problem where a small change to the LIMIT clause in a query changes the query plan to one that has vastly inferior performance. In this case, I have two ...
- 1
2
votes
2
answers
276
views
Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?
Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like:
-- typical table structure:
create table t1 (
...
-1
votes
2
answers
62
views
How to index when the fields used in the query is unpredictable?
In collection A, each document contains up to 100 fields. The database, mongoDB, only allows up to 64 indexes on a single collection.
Indexes are needed for a query on this collection. The filter, or $...
0
votes
0
answers
76
views
Mariadb index creation on large table leads to corruption
I am trying to create an index for a very large table called reception_edges_denorm (200GB) in MariaDB. The table has 4,916,267,670 rows and the following schema :
src_trs_id: bigint(20)
...
0
votes
2
answers
135
views
How to improve performance on a SQL database table that has 60 million rows
I have a table that has 60 million rows in a Postgresql database.
Table is as follows:
id varchar PK
cutomerId Integer
orderId Integer
modified timestamp.
Now I want to create a summary of customer ...
- 103
-2
votes
1
answer
29
views
Solution to query that must examine all rows / documents in worst case
The logic of my query goes like this:
Filter documents based on arbitrarily many and non-predetermined fields. The filter conditions can total to hundreds. But the worst case is that there are no ...
0
votes
1
answer
102
views
How much memory using for adaptive hash indexes in MariaDB?
I'm reading about adaptive hash indexes in MariaDB. It is created in memory, so it will be logically to know how much memory usually used for the index table.
For example, my local development ...
- 111
0
votes
2
answers
41
views
Optimizie MySQL query: col_name IN (val1, ..., valN)
I had posted a question about mysql equality range optimization here.
At this post, I want to discuss about why mysql optimizer is too bad for this type of query: col_name IN (val1, ..., valN).
My ...
- 145
0
votes
0
answers
47
views
Utilizing GIN Indexes Efficiently for Queries Without Type Casting in Postgres
We have an use case where we need to perform cascading deletions across multiple tables in our database. For this purpose, I have found that using GIN indexes as suggested in this answer is quite ...
- 11
0
votes
1
answer
187
views
PostgreSQL Partitioning and Indexing - Optimal WHERE Clause for Partition Pruning
I have a PostgreSQL table named orders, which I have partitioned based on the user_idcolumn using LIST partitioning using,
PARITION BY LIST lower(right(user_id, 2))
The table has columns customer_id ...
- 213
2
votes
1
answer
323
views
Optimal order for creating a composite index in PostgreSQL with multiple conditions
I have a table with three columns: user_id, customer_id, and order_id.
In my queries, I frequently filter the data using conditions like,
... WHERE user_id = 23434 AND customer_id = 234234 AND ...
- 213
0
votes
2
answers
192
views
MySQL 8 is not indexing on Functional index
I'm using MySQL 8.0.30 and known that it's supporting for Functional index. However I tried to create an index base on sum of some fields, it's very easy as below, but index is not used, even in ...
- 111
0
votes
1
answer
42
views
Different index selection and join strategy between QA and Prod environments in PostgreSQL
I have encountered a scenario where the same query on a PostgreSQL database is exhibiting different index selection and join strategies between the QA and Prod environments. I'm trying to understand ...
- 213
0
votes
1
answer
53
views
How can I make (game_id, user_id) unique, yet (game_id, score) indexed/clustered, in ScyllaDB?
See this in ScyllaDB:
CREATE TABLE scores_leaderboards (
game_id int,
score int,
user_id bigint,
PRIMARY KEY (game_id, score, user_id)
) WITH CLUSTERING ORDER BY (score DESC);
The ...
- 809
0
votes
0
answers
387
views
MySQL indexes on temp tables
I am trying to perform joins between two temporary tables, creating an index on the join key for each temp table. However, when I EXPLAIN the join, the row estimates always seem to come out vastly ...
- 1
0
votes
2
answers
134
views
Is there a benefit to using a surrogate key for a many-to-many table?
It is understood that a table with a clustered index with non-sequential value(s) will cause fragmentation due to page splitting.
In my case, I'm working with a pair of uniqueidentifiers in a many-to-...
0
votes
1
answer
120
views
PostgreSQL query scanning all partitions instead of specific one
I created my table as below:
CREATE TABLE IF NOT EXISTS public.customers (
user_id varchar(255) NOT NULL,
item_name varchar(255) NOT NULL,
score int4 NULL,
is_marked bool NULL
) ...
- 213
0
votes
2
answers
166
views
PostgreSQL query using BRIN index is slow until REINDEX/VACUUM
PostgreSQL 15.2. I have a partitioned table like this:
create table event
(
dataset_id integer not null,
event_id integer not null,
second_offset integer not null,
-- other columns
) ...
- 190
0
votes
1
answer
86
views
MySQL 5.7 to MariaDB 11 migration index problems
I'm having a problem with one big SQL. We're migrating a MySQL 5.7 database to a MariaDB 11, and the majority of queries are running faster, but i've problems with this one, and I don't know how to ...
- 121
0
votes
1
answer
27
views
PG not using index in queries with functions: `in (select some_func())`
I have a select with sub-select in where:
select * from injections where id in([some select]);
It uses an index and everything is fine. But now when I move the sub-select statement into a function ...
0
votes
1
answer
75
views
Clustered index on primary key vs on non-unique column
As a starting point, is it better to use the surrogate identity column (SaleItemID) as the clustered index. Or look for a column that I expect will be used often when reading data from the table but ...
- 503
0
votes
4
answers
247
views
Indexes for speeding up find by rank / counting
Consider a table with a single id which has a PRIMARY KEY index:
CREATE TABLE test (id INT PRIMARY KEY)
Now consider queries like
SELECT COUNT(*) FROM test
SELECT * FROM test ORDER BY id LIMIT ?, 1
(...
- 143
3
votes
1
answer
396
views
Are individual indexes necessary on columns that are included in a compound primary key?
I have this M2M join table:
CREATE TABLE [dbo].[RecipientsDonors]
(
[RecipientId] [int] NOT NULL,
[DonorId] [int] NOT NULL,
CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED
(
[...
- 549
0
votes
4
answers
120
views
Choosing the index partition type for OLTP application
The ORDER_ITEMS table holds the data related to the orders.
Use case: OLTP. The rows will be added and the historical data(more than 3 years old) will be deleted periodically. Partition/Index ...
- 1
0
votes
0
answers
220
views
Cursor style pagination with timestamp + id with a filter on timestamp
I have an events style table with enough data to be a problem, but nothing insane. I do need a bit of help the most appropriate index or query pattern for cursor based pagination, since I am finding ...
- 263
2
votes
1
answer
50
views
Inefficiency of index in SELECT requests after large INSERT requests on myisam tables on MariaDB 10.6
This is my first post on this website. Please don’t hesitate to indicate me if there is any problem in my post.
I have a new issue when migrating from MariaDB 10.1 to MariaDB 10.6 on indexes with ...
- 21
0
votes
1
answer
32
views
Is putting a column as first in clustered primary key the same as giving its own clustered index?
Perfomance-wise, is it the same to query a table on a non-unique GUID in these two cases?
Case 1: Primary key on multiple columns while the significant column is the first item in the primary key
...
- 103
0
votes
1
answer
404
views
Partitioning strategy and optimal number of partitions for varying data formats (string, numeric, and UUID) in a large table
I'm working on designing a partitioning strategy for a large table in my database, and I'm considering using hash-based partitioning. I came across a recommendation to have the number of partitions ...
- 213
0
votes
2
answers
239
views
Unable to drop file group because it is not empty
I am unable to delete a filegroup. I am getting the standard error: "The filegroup '~~~~' cannot be removed because it is not empty." I have been searching the internet, and still cannot ...
- 332
0
votes
2
answers
128
views
How to index JSONB column with dynamic keys
I have a table tickets which can contain custom fields. It's implemented using a jsonb column tickets.custom_fields, which contains data in the format { "<field_id>":<field_value>...
- 101
1
vote
1
answer
41
views
JOIN with a small table causes execution plan to change and query to slow dramatically
I have the following tables:
CREATE TABLE acl_sid (
id bigserial not null primary key,
principal boolean not null,
sid varchar(100) not null,
constraint unique_uk_1 unique(sid,principal) );
...
- 169
-1
votes
1
answer
173
views
How should I handle index maintenance on partitioned tables in Azure SQL DB?
let's start saying that i have no experience with partitioned tables and i'm not quite sure how to handle index maintenance (periodically Rebuild / Reorganize, if needed, to reduce index fragmentation)...
- 1
0
votes
1
answer
77
views
Indexing of hash-value column vs INT IDs for session tokens
Let's say:
your application should use session IDs generated with a CSPRNG, which shall authenticate your user sessions
your system uses PHP 8.2 and MariaDB 10.6
Which of the approaches below ...
- 163
0
votes
1
answer
70
views
sys.indexes contains a row per index or heap of a tabular object - what does it mean?
I know a HEAP is a table without a clustered index. But I'm not quite clear on the following definition from sys.indexes.
I understand that this system view contains row per index of a tabular object. ...
- 289
1
vote
1
answer
152
views
Write latency for adding an index on read replica
This is a question that I've not been able to find any good documentation for on the internet.
I think it's a common industry standard that for tables with heavy writes, you add an index on the read ...
- 131
2
votes
1
answer
111
views
What is the internal representation of a multi-column GIN index in Postgres?
Let's have a table:
usr
terms
usr1
[v1,v2]
usr1
[v3]
usr1
[v1]
usr2
[v1]
usr2
[v2]
and assume we create a multi-column GIN index on it.
CREATE INDEX my_gin_ix ON items USING gin (usr,terms);
...
- 133
1
vote
2
answers
85
views
Custom Operator Class for BigInt Using Bloom Indexes Not Working in PostgreSQL
I'm currently dealing with a large PostgreSQL table containing approximately 350 million rows. For performance optimization, I'm trying to create Bloom indexes on some bigint columns in my table.
...
- 11
1
vote
0
answers
66
views
Super slow index scan from IN subquery (generated by third party software). Tuning postgres or rewriting query?
I have a query which has a huge bottleneck. Doing explain analyze I found out that an index scan is causing the problem.
The query is:
select * from product where (product.id = IN(select pu.id from ...
- 11
0
votes
1
answer
37
views
Impact of creating separate indexes on high-insertion PostgreSQL table [closed]
I'm building a taxi application that uses PostgreSQL as the database backend. One of the tables, taxi_order, has the following columns: start_address, country_id, taxi_fleet_id, and driver_id.
...
- 111
0
votes
0
answers
18
views
HardDisk seeks during reads in B Tree Indexes
B Tree Indexes are said to generally give better read performance than LSM Tree indexes. As I understand B tree indexes work by redirecting the reads to different locations on the HDD until the ...
- 272
1
vote
2
answers
52
views
MySQL ORDER BY Indexes When ORDER BY May be Unknown
For some background lets say I have two tables:
create table orders (
id int unsigned auto_increment primary key,
uuid char(36) not null,
status_id int unsigned not null,
account_id int unsigned ...
- 11
2
votes
1
answer
208
views
Mariadb: Can I increase performance by having multiple column indexes with different orders?
In my Mariadb table I have two id-columns (A,B), which combined are unique, but not individually. This table is quite large. To speed up queries like
SELECT B WHERE A=?
I have added a primary key (A,...
- 23
3
votes
3
answers
386
views
Impact of index on a "status" field with one (guaranteed) change
Introduction
I have a PostgreSQL table setup as a queue/event-source.
I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for ...
- 33
1
vote
2
answers
75
views
MySQL - Create a Covering Index where the Sorting Option Columns are Identical to the Select Payload Columns
Something to note up front, I am using Magento 2 so if the query looks a little strange, that's why.
I have the following query and am trying to optimize the indexes for the table;
SELECT `main_table`....
- 23
0
votes
2
answers
48
views
Help trying to optimize a query for active user items (where on a boolean column)
We have a production project running on MySQL with following scheme
[There is obviously many more columns but I omitted irrelevant columns to simplify the question]
# Has approximately 9 million rows
...
1
vote
1
answer
589
views
Drop unique index of a unique constraint
I'm currently working on a postgresql 15 database.
I've created a data table named "measures" with following schema
CREATE TABLE measures (
timestamp TIMESTAMPTZ NOT NULL,
value double ...
- 31
1
vote
2
answers
73
views
How to interpret the stats of index usage? [closed]
Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
- 289
4
votes
1
answer
590
views
AWS Aurora Mysql seemingly not picking PRIMARY or secondary index
Relevant System Info:
Aurora Mysql 8.0.mysql_aurora.3.03
rg6.xl instances (1 writer, 2 read replicas)
Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot)
I have been ...
- 135
0
votes
1
answer
42
views
Index consolidation of two similar indexes
We have a primary table that drives most of the work within our product with many columns.
Index 1 has a key consisting of: Column A, Column B, Column C, Column D
Index 2 has a key consisting of: ...
- 3