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,272
questions
4
votes
1
answer
7k
views
Creating index on a big text column in postgres
We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE.
The column is of type text and it is used ...
CommunityBot
- 1
1
vote
2
answers
469
views
MySQL is not using index on query plan with Order By and Limit
I'm trying to understand the behaviour of the MySQL query planner where an index is not used when a query includes Order By and Limit combined.
The table in question is big with around 8 millons of ...
CommunityBot
- 1
1
vote
1
answer
843
views
MongoDB is not using the index I created for this specific query
MongoDB 4.0.
I have a simple query:
db.product.find({
modifiedOn: {
$gte: ISODate("2019-08-02T03:00:00.000Z")
}
})
.limit(10)
And a simple index on modifiedOn....
CommunityBot
- 1
0
votes
4
answers
1k
views
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT ...
- 101
1
vote
1
answer
116
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query which takes almost 17 minutes to run:
select * from public.extrinsics
where chain_id = ...
- 172k
1
vote
1
answer
298
views
Why is my index not being used in this nested query?
I have a nested query: an inner geoquery to filter, then an outside query to get the ids of the objects.
Because this query became a speed bottleneck in an important operation, I am working on ...
CommunityBot
- 1
0
votes
1
answer
944
views
Using a full text index with PostgreSQL and Doctrine
I received the following error:
SQLSTATE[54000]: Program limit exceeded: 7 ERROR: index row size 2728 exceeds btree version 4 maximum 2704 for index "fulltext"
DETAIL: Index row references tuple (1,...
CommunityBot
- 1
1
vote
1
answer
55
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. ...
- 11.8k
0
votes
0
answers
14
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 ...
JollyRoger
0
votes
2
answers
65
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
2
votes
2
answers
606
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 ...
- 38.9k
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
1
vote
1
answer
65
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 ...
- 17.4k
1
vote
1
answer
636
views
MySQL Indexes on all column combinations
I have a table that basically represents the log of records
CREATE TABLE records
(
id varchar(36), -- uuid
text blob,
user_id bigint,
cluster_id bigint,
status_id ...
CommunityBot
- 1
1
vote
1
answer
57
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 ...
- 38.7k
1
vote
3
answers
51
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 ...
- 77.1k
32
votes
1
answer
34k
views
When to use sort_in_tempdb when rebuilding indexes?
We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (...
- 364
6
votes
2
answers
3k
views
GIN index not used when adding order clause
I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries)
SELECT *, count(*) OVER() as filtered_count FROM "users"
...
- 172k
0
votes
1
answer
548
views
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.
Do you think that indexes going to reduce query run time?
Any ideas how ...
CommunityBot
- 1
0
votes
1
answer
392
views
Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)
I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5.
After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query.
...
CommunityBot
- 1
2
votes
1
answer
77
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
1
answer
304
views
MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze
I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
CommunityBot
- 1
0
votes
1
answer
28
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
4
votes
1
answer
608
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 ...
CommunityBot
- 1
12
votes
3
answers
10k
views
Is it necessary to ANALYZE a table after an index has been created?
In my transaction, I am creating a temporary table:
create temporary table x on commit drop as
select ...
I also add an index on that table:
create index on x(some_column);
Is it now necessary to ...
- 1
4
votes
1
answer
3k
views
Postgresql 9.5 BRIN index dramatically slower than expected
I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was ...
CommunityBot
- 1
0
votes
2
answers
102
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?
...
- 833
1
vote
1
answer
578
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
CommunityBot
- 1
0
votes
2
answers
583
views
Creating a specific index tree structure from keys that have the tree-structure baked in? Indexing H3 Hexes
Preface: I am NOT a DBA, please be gentle on my terminology and explanations.
I have data that already has a structure baked into it and need to create an index in my DB that represents the ...
CommunityBot
- 1
1
vote
2
answers
2k
views
Find MySQL InnoDB index corruption
I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c.
When I run query:
SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3;
the ...
CommunityBot
- 1
0
votes
1
answer
35
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 ...
- 48k
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,.....
12
votes
3
answers
25k
views
Postgres is performing sequential scan instead of index scan
I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
- 172k
0
votes
1
answer
542
views
MongoDB Indexing - How many fields should I index?
I have a Collection in my db, called Post.
Here's how it looks:
Post {
user: <EmbeddedObject>,
school: <EmbeddedObject>,
hashtag: <EmbeddedObject>,
numberOfReports: ...
CommunityBot
- 1
3
votes
3
answers
98
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 ...
- 77.1k
3
votes
2
answers
91
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 ...
- 77.1k
7
votes
2
answers
4k
views
Does creating an index on BIT(n) attribute in MySQL make sense?
I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.
I've seen some mentions on the internet that BIT ...
- 111
0
votes
0
answers
18
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
1
answer
48
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 ...
- 3,780
0
votes
1
answer
1k
views
MySQL How to Script to create indexes on all date columns in database
I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index .
So far I ...
CommunityBot
- 1
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 ...
- 8,269
1
vote
0
answers
37
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 &...
0
votes
1
answer
1k
views
mysql creating sort index making queries slow
We have a view table the query for the view is pasted below. The problem is that whenever we try to query items view our database start making sorting index which make the over all time of our ...
CommunityBot
- 1
3
votes
1
answer
3k
views
How to optimize a Full Text Search
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.
Doing this search slows the query down by about 300ms and since the query is run a few ...
CommunityBot
- 1
0
votes
1
answer
3k
views
MariaDB 10.6 join query 10 times slower than 10.3
I have two VPS servers (LEMP) running Wordpress sites. Specs:
Old
4GB RAM
Ubuntu 18.04
MariaDB 10.3
New
8GB RAM
Ubuntu 22.04
MariaDB 10.6
I migrated a site and found that it was around 10 times ...
CommunityBot
- 1
0
votes
1
answer
4k
views
"Cannot continue the execution because the session is in the kill state" when querying system tables
This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and ...
CommunityBot
- 1
1
vote
3
answers
377
views
Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?
Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work?
My mind is ...
CommunityBot
- 1
1
vote
2
answers
773
views
mysql - large table - index performance and query issue
I have simplified a much larger table below:
CREATE TABLE `core` (
`id` int NOT NULL,
`loc_country` enum('United States','Colombia','United Kingdom',
'Australia','India','Germany','...
CommunityBot
- 1
2
votes
0
answers
81
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
138
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 |...
- 349