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,273
questions
0
votes
0
answers
9
views
aurora mysql index creation challenges on large tables
I have a table of yearly data [size 500GB = ~1 billion rows] in aurora mysql 3.02.2. requirement is to migrate this table to another aurora mysql [PROD] using DMS. we initiated the DMS task with data ...
0
votes
1
answer
13
views
How do I find missing sub-partitions of an index to make main index complete?
I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create ...
- 111
0
votes
1
answer
49
views
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
2
votes
2
answers
585
views
Why are these columns specified in the include clause of an index rather than as key columns
For the below query, I'm trying to figure out why the index suggested by SQL Server on the sysjobhistory table, which is also the index that results in a seek, was created on the Job_Id column with ...
- 47
1
vote
1
answer
60
views
Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?
I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
0
votes
2
answers
59
views
Index optimization for generic UI sorting and filtering [duplicate]
We have a table that may contain 1M-5M or more rows.
It has an assortment of string, numeric and date columns.
The UI presents a table view, with "infinite scroll" paging, and various ...
- 101
1
vote
1
answer
55
views
potgresql uses only one worker for index creation
To speed up the CREATE INDEX operation I increased the work_mem and set max_parallel_maintenance_workers to 8 and it worked as expected when I was creating a B-tree index on the table. I saw 7 ...
- 113
1
vote
3
answers
50
views
MySQL query optimisation
This is the MySQL table DDL:
CREATE TABLE `users` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
`nick` text NOT NULL,
`subscription` char(1) NOT NULL,
`ask` char(1) NOT ...
0
votes
1
answer
25
views
Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters
I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle ...
- 1
2
votes
1
answer
76
views
Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?
I'm a software engineer with about 10 years of experience building Laravel web applications for small businesses. I'm pretty comfortable with database design, but it's not my greatest strength.
One of ...
- 23
0
votes
2
answers
101
views
Does the logical query processing change when indexes are involved?
I highly recommend this book.
This book explains in a great way the behavior of a sql query, but my doubt is,
How is a sql query processed when there are indexes, that is, would this behavior change?
...
- 1
0
votes
1
answer
34
views
What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL?
I would like to know what the difference is between CREATE INDEX and CREATE INDEX CONCURRENTLY (if any) after the creation of the index has built. I have been having a discussion with a colleague ...
0
votes
0
answers
42
views
Complex day-to-day difference of historical data in SQL server
Setting:
I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
1
vote
1
answer
45
views
MySQL - Slow Performing Subquery when DISTINCT Is Used
I have this query that runs very slowly when DISTINCT is used;
SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
- 23
0
votes
0
answers
17
views
Verify a postgresql index can be dropped securely
Indexes use resources because they have to be updated when data changes.
How to check if an index can be securely dropped (ie. not used at all)?
This request seems to return the unused indexes, ...
- 235
1
vote
2
answers
65
views
Should a frequently-growing log file be stored as a table or a blob in the database?
I'm an undergrad and haven't worked with any actually database yet so my apologies if I misunderstand/misuse any terms. My question comes from an assignment of mine, but this is my attempt at learning ...
- 13
1
vote
0
answers
36
views
Postgres 15 doesn't use index under RLS
Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4 and noticed extremely high disk consumption on the following query
execution:
select (exists (select 1 as "one" from &...
3
votes
3
answers
83
views
Do you need to explicitly include the primary key in a composite index?
I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these ...
- 131
3
votes
2
answers
87
views
MySQL chooses a slow execution plan for selecting rows by range condition with ORDER BY, LIMIT and OFFSET clauses
I have a table "client_data" with 2 indexes:
CLIENT_USER_CREATED (CLIENT, USER, CREATED)
CLIENT_CREATED (CLIENT, CREATED)
The primary key is (ID, CLIENT, CREATED)
The table is partitioned ...
0
votes
0
answers
35
views
Mysql: Compare 2 types of JSON data ["a", "b", "c"] vs {“a”: true, “b”: true, "c":true}
I want to create a field like unordered_set (unique elements) in C++. In mysql, I use JSON field, there are 2 ways to do:
tags = [“a”, “b”, “c”, …], application layer will ensure about uniqueness.
...
- 145
2
votes
0
answers
79
views
Query not using foreign key indexes
I have the following schema for my database:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employments (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
...
1
vote
1
answer
132
views
Can PostgreSQL use indexes of both columns in a query condition and order by clause in a single query?
I'm running PostgreSQL 11 with shared_buffers set to 3 GB on my Mac. I have a table job with 5 million rows. The table structure is
Table "public.job"
Column |...
- 113
0
votes
2
answers
120
views
Postgres indexes for 75 columns
I have a table with about 5 million records in my Postgres database for real estate listings in my area. The table has 95 columns, ~75 which are used for filtering and searching for listings the user ...
- 3
1
vote
2
answers
583
views
How to optimize a query on a large table, besides using index?
I need suggestions/ideas of optimizing the following query:
SELECT COUNT(*)
FROM (SELECT 1
FROM f
WHERE parent_sha256 = $1
AND parent_sha256 <> sha256
LIMIT 1) AS ...
- 123
0
votes
2
answers
81
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
- 101
0
votes
2
answers
38
views
The effectiveness of compound index where the prefix is continuous / high cardinality
schema:
{
time_utc: "milliseconds",
city: "string",
age: "integer"
}
index:
{
time_utc: 1,
city: 1,
age: 1
}
sample query:
col.aggregate([
{
$match: { ...
1
vote
1
answer
84
views
Index is not used when doing a query with PostgreSQL CTE
Given I use the following table :
CREATE TABLE operations (
asset_id varchar(255) NOT NULL,
event_id varchar(255) NULL,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
and ...
- 113
4
votes
2
answers
919
views
Balancing Indexing and Database Performance: How Many Indexes Are Too Many?
I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default ...
- 1,149
0
votes
1
answer
48
views
Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint
I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.
Here is a simplified version of my T-SQL code:
IF OBJECT_ID('dbo.dept') IS NOT NULL ...
- 1,149
1
vote
1
answer
35
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 13
2
votes
1
answer
67
views
Why does creating a compound unique index with an fk and text column fail for mariadb?
I encounter an error when trying to create a compound unique index using a foreign key and a TEXT column.
The error can avoided by explicitly adding an index on the foreign key or changing the type of ...
- 121
0
votes
0
answers
31
views
Why doesn't PostgreSQL lookup by PK first (when `WHERE` contains more conditions)?
When I run this against my PostgreSQL DB (where the id column on the courses table is the primary key, and there's also an index on is_deleted, which is a boolean):
SET enable_seqscan = OFF;
EXPLAIN ...
- 131
0
votes
2
answers
33
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
85
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
64
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
869
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
-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
56
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
23
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
57
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 ...
- 564
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
31
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
53
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
101
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
61
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
56
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
68
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
114
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
46
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