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,243
questions
0
votes
2
answers
28
views
What is the correct index on a large dataset with group by?
I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency.
I have several instances I need to manage where I need to sort ...
- 185
0
votes
1
answer
55
views
How do I query out the largest value using an index
I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so
create index index_events_on_created_at_and_bucket_id
on ...
- 157
0
votes
2
answers
37
views
Partitioning a Table Vs Partitioning a clustered index
If I have a Table in SQL server, with Time series data for every business date (~100k rows a day)
and only the last 45 days are frequently updated/deleted/inserted, it would be better to partition ...
- 1
5
votes
1
answer
635
views
Adding a Primary Key to a Large PostgreSQL Table with High Traffic
I need to add a primary key to a large PostgreSQL table (approximately 2TB) with high traffic. This is a critical operation, and I'm looking for guidance on how to do it efficiently.
I have already ...
- 213
-2
votes
0
answers
28
views
How to put indexing in this query,query takes 85 sec to complete
Select
count(case when education_qualification='A' then 1 else null end) as Total_Niraksar,
count(case when education_qualification='B' then 1 else null end) as Total_Saksar,
count(case when ...
-1
votes
1
answer
26
views
Indexing system vs raw DMBS connection
I have created a new dataset that has 11,000,000+ rows with 4 pivot tables in MySQL. The tables are not that deep, only 6 - 12 cols per.
My question is I've set up the Lucene Apache SOLR to index the ...
- 113
1
vote
1
answer
36
views
Create an index on a very large table to filter on date, or date + id
I am creating a table where I will have a large volume of data and the search will always be based on a date, or a date + an ID. I want to create an index to support the queries, but I have no idea ...
- 123
0
votes
0
answers
13
views
MongoDB indexing for dynamic query
I'm trying to build a query optimizer for survey response data, all questions are stored in a collection and have some fields are number_of_selection, option_marked etc.
Now I'm trying to build a ...
0
votes
2
answers
50
views
Is a Composite UNIQUE Constraint On an Already UNIQUE Column Redundant?
I'd just like to clarify something pretty simple regarding single and composite UNIQUE constraints that I can't find a clear answer to.
Assume I have the following table:
CREATE TABLE person (
id ...
5
votes
2
answers
2k
views
What order should my index's columns be for a SELECT a, b, MAX( c ) FROM d WHERE e = 1 GROUP BY a, b?
Supposing I have this table (in a multi-tenant SaaS database:
CREATE TABLE dbo.Messages (
TenantId int NOT NULL,
RecipientId int NOT NULL,
MessageId int ...
- 554
0
votes
0
answers
14
views
Setting indexes right
Given following SQL query
SELECT dd.* FROM devices_deviceconfiguration_sensors dds
JOIN devices_latest_config dlc ON dlc.config_id = dds.device_configuration_id
JOIN devices_deviceconfiguration dd ON ...
- 127
0
votes
0
answers
26
views
Create index on array of composite types
I have this example
create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');
create type price_element as
(
price_start ...
- 417
0
votes
1
answer
37
views
Optimizing PostgreSQL Query Using Composite Index and GIN Index
I have a PostgreSQL query that involves multiple conditions and joins. While I've created a composite index to speed up some of the filtering, I'm still experiencing extra rows being fetched due to ...
- 213
0
votes
1
answer
71
views
How to optimise a query with inner join and order by
I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
- 101
0
votes
1
answer
44
views
Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering
I'm working on optimizing a query for a PostgreSQL database used in an educational platform.
The query retrieves distinct question_id values along with their is_seen status from the ...
- 213
0
votes
1
answer
18
views
mysql table tables_priv index is corrupted and the best possible way to repair it
I am using MySQL with XAMPP.
For sometime now I keep getting a server unavailable error and upon digging deeper I noticed that my mysql.tables_priv table index is corrupted (ARIA Database Type). I ...
1
vote
1
answer
54
views
PostgreSQL query optimization issues
I am trying to optimize a query on a PSQL table with ~14M rows, which normally would take ~10seconds to run:
SELECT r.cust_id as cust_id, r.payable as toBePaid, r.return_code as returnCode, COUNT(r) ...
2
votes
2
answers
105
views
Getting wrong/misleading info related to oracle table index
Creating a table as follows:
CREATE TABLE "HOTEL3"
( "NAMEX" VARCHAR2(4000 CHAR),
"CITY" VARCHAR2(4000 CHAR),
"PRICE" NUMBER,
"ID&...
- 189
1
vote
1
answer
31
views
Can't use performance_schema in my mysql server
I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking ...
- 11
0
votes
1
answer
39
views
How to conditionally recreate indexes created by `CREATE INDEX ON <table_name> (<column_name>)`?
I am squashing migrations and rewriting all constructs like:
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
description text
);
CREATE INDEX ON ...
- 276
0
votes
1
answer
42
views
Does a long string with "unique" constaint take a lot of space?
I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:
global_settings:
id primary key,
key ...
- 35
0
votes
1
answer
55
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
74
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
269
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
44
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
42
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
128
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
28
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
48
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
29
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
53
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
192
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
81
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
41
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
39
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
163
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
105
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
63
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
98
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
68
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
71
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
194
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
393
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
85
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
105
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
38
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
213
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