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
0
votes
1
answer
519
views
How should I configure my postgresql to create an Index for a big table (655GB) as fast as possible?
I'm importing a big dataset of medication usage (14 years of public health system data from Brazil) for a data science project.
When I first started, I created one of the tables with all the indexes (...
- 103
0
votes
2
answers
123
views
Large table with inactive records. Better performance to move inactive records?
We have a database which most tables utilise an inactive flag. Everytime a record is changed the active flag is set to 0 and a new record is created. This enables us to keep an auditlog of all ...
- 131
0
votes
2
answers
80
views
Does the index in InnoDB and MyISAM keep amount of child nodes in B-trees?
Does the index in InnoDB and MyISAM keep amount of child nodes in B-trees ?
If I have an b-tree index on (field1, field2) and we are dealing with MyISAM (to avoid issues with transactions, locking, ...
1
vote
2
answers
198
views
AWS RDS MariaDB update from 10.2 to 10.3 now has slowed my queries
This AWS RDS MariaDB server has been running for 5+years now.
After the automatic Amazon RDS update from 10.2 to 10.3, the server had downtime (during the update), then after it went online, the read ...
0
votes
1
answer
53
views
How to optimize index if expected result is limited to 50k?
I have a collection where we are checking the recently active 50k users.
The collection has around 1m data.
And the query that we are using is this:
db.getCollection("UserLastActive").find({ ...
- 129
1
vote
1
answer
416
views
Query performance tuning on partitioned table
I have a simple table partitioned by Date for each day
so ranges are like ('2023-03-07 23:59:59.997', '2023-03-08 23:59:59.997') etc
with about few millins records per each range
the table is as ...
- 13
0
votes
2
answers
119
views
Terrible performance on WP-generated query migrating from MySQL to MariaDB
I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes.
There's one query in ...
- 21
0
votes
1
answer
602
views
Does atomic_refresh=>false in dbms_mview.refresh procedure rebuild index?
Recently I've receive some alerts of unusable materialized view indexes and it happens when a materialized view is refreshed. The materialized view is refreshed with this command:
exec dbms_mview....
- 630
0
votes
0
answers
194
views
Efficiently Filter Parent Table with Join to Two (or more Children Tables)
Given the following schema for Microsoft SQL Server:
CREATE TABLE Parent (
TenantId int,
Id int,
ChildAId int,
ChildBId int,
);
CREATE TABLE ChildA (
TenantId int,
Id int,
);
...
- 522
0
votes
1
answer
103
views
MySQL index of column which needs count by prefix
My table (InnoDB) has a column name varchar(255) NOT NULL, which consists of such data:
A:something
B:another
... ...
I want to make the following query fast, when the data grows:
SELECT COUNT(*) ...
- 145
5
votes
3
answers
448
views
Can Postgres scan indexes backwards?
We use an Amazon RDS instance with
PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
I have a simple classic top-1-per-group query. I need to ...
- 4,577
5
votes
2
answers
1k
views
Is the IN() condition always an equality search?
I am trying to understand if the IN() condition is an equality or inequality search when it comes to indexing.
I have been looking on the internet for an answer and have found responses ranging from, ...
- 153
3
votes
3
answers
1k
views
How to index a table using column of another table having relation with it?
I have two tables, Pilot and Company. My queries will be searching for the Pilots using the CompanyName. For example, I want to get Pilots in a company named 'Boeing'. How should I write my CREATE ...
-1
votes
1
answer
181
views
SQL Server 2014: Why is Clustered Index Rebuild MUCH faster than initial index creation? [closed]
I have a table where rows consist of a few GUID ids and then a huge byte array of data. There is a partitioned clustered index on this table, which orders data by the GUIDs.
The table is large, ...
- 9
2
votes
0
answers
70
views
Clustered Index being used over non-clustered with same key
I have a table that has a clustered index (CI) on the field filename.
It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
- 29
1
vote
1
answer
260
views
Force Index does not work on aws mysql
I have a table sales with a compound index on (user_id, eastern_date, state)
CREATE TABLE sales (
id int not null auto_increment,
user_id int not null,
eastern_date date not null,
...
- 113
4
votes
1
answer
1k
views
Postgres: Create unique index with conditional
I have a table in Postgres with records of type [event_id, user_id, status]. The user is scoped per event, so I have a unique index on [event_id, user_id] so that a user has a unique status. The ...
- 141
2
votes
1
answer
115
views
'Group by' on indexed TEXT columns not working
I'm creating the following table in MySQL:
CREATE TABLE group_by_test (
txt1 TEXT,
txt2 TEXT,
txt3 TEXT,
val INTEGER,
INDEX (txt1(10), txt2(10))
);
I then insert a bunch of rows, ...
- 155
0
votes
1
answer
52
views
Creating an index or not creating an index
I have a SQLite database and a table, which can grow up to 100.000+ rows.
I need to query data in the table really (!) fast. There are two ways to query the data.
I have an index of type string, ...
- 177
16
votes
3
answers
3k
views
What's the use case of disabling an index?
I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can't access the index or the data if clustered index. When will we disable an index?...
- 1,159
3
votes
2
answers
193
views
Can a redundant index improve SELECT performance?
Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how?
The MariaDB 10.4 ...
- 153
0
votes
1
answer
2k
views
Unique constraint on partitioned table
PostgreSQL has a limitation on unique constraints on partitioned tables:
Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation ...
- 465
0
votes
1
answer
449
views
Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)
On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric.
I have 4 Millions rows in my ...
- 1
5
votes
1
answer
668
views
Index Design: clustered index using a DATE
Every once in a while, one needs a check. This is such a time.
I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" ...
- 177
0
votes
1
answer
79
views
Unique index on 3 columns with 2 mutually optional
Given this table
create table contacts (
customer int not null,
phone char not null,
facebook_id char not null
/* some other fields */
)
I'd like to make sure there can't be two rows ...
- 101
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 ...
- 1
2
votes
0
answers
199
views
Indexing 250M URLs for infix search in PostgreSQL
I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
- 183
0
votes
0
answers
168
views
Postgres query plan different on local and prod. Not using index and bad row estimation
Confusing index-use question, I have a database that looks like this:
+-----------------+------------------+-----------+----------+--------------+-------------+
| Column | Type | ...
- 101
0
votes
1
answer
100
views
MySQL add key to a table that is in use
I am running MySQL server v5.7.18 on CentOS 7.
One of the tables is used as operations log for a web API, and has grown to 20 million rows.
Users search this table from a front end app which runs a ...
- 103
3
votes
1
answer
117
views
Maria DB - Is there a point in indexing each uniq index cols separately?
We are running 10.2.44-MariaDB-log MariaDB Server. Right now I am creating tables with this structure
Primary('id')
UniqueIndex('colA', 'colB', 'colc');
Index('cola')
Index('colb')
Index('colc')
I ...
- 259
4
votes
1
answer
554
views
Index on expression (regular expression pattern matching) is not used in query
In my PostgreSQL 12.8 database, I have a relatively simple table the_table with a column value with type varchar:
CREATE TABLE public.the_table (
id uuid DEFAULT gen_random_uuid() NOT NULL,
...
- 143
0
votes
0
answers
292
views
What should I choose between jsonb array and jsonb map for faster querying?
I have a jsonb column which will consist of some keys and the rows will be searchable using those keys. Considering that I will be applying GIN index on the jsonb column what should be the ideal ...
2
votes
1
answer
75
views
Adjusting Index for Better Performance
I've just started my journey as a dba and I figured out using a script that my database indexes are a mess. Here they are:
And here is what the script tells me about them:
Can you give me insights ...
- 35
6
votes
7
answers
2k
views
What is the benefit of indexing assuming that SQL server has huge memory to accomodate the entire database in memory?
I understand that index seek allows the server to quickly go to the desired page(s) by looking up the index, therefore the benefit is obtained by not having to read all pages of the queried table from ...
- 3,442
8
votes
2
answers
905
views
Index seek to specific multi-column key, then get some rows in lexicographical order
Consider the following sample table with a multi-column index:
create table BigNumbers (
col1 tinyint not null,
col2 tinyint not null,
col3 tinyint not null,
index IX_BigNumbers ...
- 755
0
votes
2
answers
729
views
Optimizing IN clauses with thousands of integers
I have a search engine that outputs the id's of the elements to show in the search results, or to use in an "analytics" module.
In this one, I perform a lot of different queries to show ...
- 101
0
votes
2
answers
4k
views
Difference between PRIMARY KEY and SERIAL (with index)
Is there any difference between a table with a field defined as
userId serial PRIMARY KEY
and a table with a field defined as
userId serial
and then having an index created on the field userId?
...
- 1,125
1
vote
3
answers
960
views
MySQL: Improve performance on large table for dynamically built query with composite index correctly
Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
- 215
2
votes
1
answer
3k
views
Detect corrupted Indexes
How to detect corrupted indexes in PostgreSQL?
When I pull data from a column with primary key attribute in a table with where condition, it brings incomplete results. Likewise, when I sort with order ...
- 21
6
votes
2
answers
2k
views
SQL Server: What's the Difference Between a 'Singleton Lookup' and a 'User Lookup'?
Microsoft describes the [user_lookup] counter in dm_db_index_usage_stats as the 'Number of bookmark lookups by user queries.' It describes the [singleton_lookup_count] in ...
- 147
-1
votes
1
answer
947
views
Deadlocks caused due to Keylocks and Pagelocks
I have a database on which select, update and insert queries are run.
At times it gave me deadlocks (key locks) while updating and selecting data from the table, it considered the select query to be ...
3
votes
2
answers
836
views
Why MySQL 8 doesn't return any rows when using a table with a descending PRIMARY KEY, 2 indexes, and valid join conditions with existing rows
Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.
When I ...
- 209
0
votes
2
answers
170
views
Key value alongside SQL table
I have a SQL (MSSQL) table storing data like the following, with id as primary key.
id
col1
col2
col3
data
valid
1
A
B
C
1
2
A
B
C
0
10
X
Y
Z
1
15
X
Y
Z
0
75
A
B
C
1
99
X
Y
Z
1
The kind ...
0
votes
1
answer
131
views
Does MySQL store the secondary indexes and the clustered index in same .ibd file?
In a file-per-table tablespace, are the secondary indexes stored in the same .ibd file as the clustered index?
haoyu wang
0
votes
0
answers
336
views
Object necessary for warmstarting database cannot be altered ORACLE
I have this error when I want to rebuild system index :
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (...
- 1
0
votes
1
answer
310
views
How to reduce internal fragmentation while deleting rows in bulk
Context
I have a table in SQL Server with millions of rows and a primary key (by default, clustered key).
I got the requirement to do housekeeping for that table, so I need to delete rows in bulk ...
1
vote
3
answers
158
views
Forcing InnoDB to build the secondary index
When INSERTing, InnoDB defers the building of secondary indexes. For example,
CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
...
- 4,491
3
votes
1
answer
231
views
Reducing Table Size after Clearing TEXT Column Precautions
I have 3rd part app with a table that's 450GB which contains file attachments in a TEXT column (yeah, I know). With a document retention angle, I've exported about 35% of the older attachments to ...
- 31
0
votes
1
answer
37
views
In general if you were to index all columns would that be the same as indexing no columns?
The title likely sums up my question, but to be clear, I'm specifically thinking RDBs.
I have no intention of doing this, I've just been curious and haven't found a clear answer in my attempts at ...
- 127
0
votes
1
answer
166
views
Query performance with integer field larger than value, partitioned by another column
Our postgres 9 Events table contains 1 Million records, comprising of 2 fields: Job and Task (job is varchar; task is integer)
A single Job might have multiple sequential Task(s), for example:
Job
...
- 101