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,316
questions
3
votes
2
answers
427
views
How to gather Index usage information
Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
- 291
1
vote
1
answer
164
views
What's the fastest way to query multiple tables at once where each table represents one year of data?
I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
- 111
0
votes
1
answer
38
views
Indexing on ANY queries
I have a table that I am trying to query. Here is the create statement for the table
CREATE TABLE IF NOT EXISTS public.TableName
(
i integer NOT NULL,
t integer NOT NULL,
v ...
- 101
0
votes
2
answers
121
views
sys.indexes - type column
According to official documentation of sys.indexes, type = 2 -->Nonclustered rowstore (B-tree). But the line 4 of the following example taken from online here is showing type = 2 -->Nonclustered ...
- 291
1
vote
2
answers
51
views
How to optimize this query or these many indexes?
What am I doing wrong here?
I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).
How to optimize that one query to run faster ?
...
- 195
3
votes
1
answer
2k
views
How to make a Postgresql function parallelly running?
I have thousands tables with same structure, and I need to add a index on each.
In order to simplify my work, I code a function like this:
CREATE OR REPLACE FUNCTION createIndexTD(
IN tbl_name ...
- 347
2
votes
1
answer
316
views
Create indexes for pagination API
I have a table event (id, user_id, category_id, source_id, start_time, is_read) with 8M records. I need to list events with paging for 6 types of APIs below (each API needs 2 DB queries)
API 1
Count ...
- 145
0
votes
1
answer
21
views
Tune indices when update multiple fields on PostgreSQL
On a PostgreSQL 14.5 database, there is the following table:
CREATE TABLE IF NOT EXISTS public."Points"
(
"Key" character varying(50) COLLATE pg_catalog."default" NOT ...
- 103
0
votes
1
answer
83
views
ASC vs DESC index when querying "smaller than"
I have a table populated very often with new entries.
Records older than 2 days get deleted every few minutes with:
DELETE FROM ip_log WHERE timestamp < NOW() - INTERVAL 2 DAY
This is the only ...
- 362
-1
votes
2
answers
224
views
Are there any differences relating to query performance between >= and <= vs. Between clause?
Are there any performance-related differences between those two queries?
SELECT * FROM `v1` WHERE `siteId`=1008 AND `date`>='2022-10-01' and `date`<='2023-02-01';
SELECT * FROM `v1` WHERE `...
- 218
0
votes
1
answer
72
views
Improving the performance of STEquals
We have a table containing all polygons (geometry/geography) of the objects in our application.
This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the ...
- 119
0
votes
1
answer
42
views
Creating a partial index on non-default values and searching all rows in that index in PSQL
If I have a table where 99% of rows with column x has a default value and want to search for a specific value in that column, will creating a partial index on x provide any improvements?
For example,
...
1
vote
0
answers
112
views
Seeking clarification and recommendations on unexpected patterns in Postgresql index usage statistics
I am trying to prune unused and rarely used indexes based on the following stats:
pg_stat_user_indexes.idx_scan: Number of index scans initiated on this index
pg_stat_user_indexes.idx_tup_read: ...
- 111
4
votes
4
answers
483
views
Is there a way to disable logging / backups for view indexes?
We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but ...
- 143
0
votes
4
answers
2k
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 ...
0
votes
1
answer
61
views
Large table with too many indexes stalls my app
PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in ...
- 101
2
votes
1
answer
968
views
How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?
I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning:
Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
- 173
1
vote
1
answer
139
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 = ...
- 125
1
vote
1
answer
230
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 113
0
votes
1
answer
69
views
Why don't I get Index Seek for my non-clustered index
Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 (Azure Managed Instance)
I'm working with a table (ActivityAttributes) that has ~190Mn rows. I've created an Index as below.
CREATE NONCLUSTERED ...
- 1
7
votes
3
answers
3k
views
Why not use two columns instead of collation for case insensitive data?
I need to store emails (like '[email protected]') in Postgres 15, be able to search them in case-insensitive manner ('[email protected]', '[email protected]', etc are the same), and be able to retrieve the ...
- 311
8
votes
4
answers
2k
views
Does creating an index and then deleting it return the database to the pre-index creation state?
I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified).
I have a backup of the ...
- 183
1
vote
1
answer
188
views
Why can't three non-unique indexes be used to enforce a unique constraint?
I have a large PostgreSQL table of 3 columns, I created a separate btree index on every single column. Then, I created a multicolumn unique constraint on the table on all the 3 columns, expecting the ...
- 11
2
votes
2
answers
848
views
Index for searching in column where the column starts with the provided value
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
id
domain
column1
column2
1
example.com
value1
value2
2
example.org
value3
...
- 123
3
votes
1
answer
1k
views
Add composite primary key on existing PostgreSQL table
I have a table on PostgresSQL with millions of rows. The table has columns a and b. I already have a unique index on a and b:
CONSTRAINT a_b UNIQUE (a, b)
I want to add a composite primary key on ...
- 133
1
vote
1
answer
602
views
Why my clustered index causing deadlock (Keylocks and Pagelocks)
We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously.
We are actually ...
3
votes
1
answer
223
views
Does simple key value storage on MySQL/MariaDB requires index on key?
I was exchanging with another developer regarding the performance of SELECT queries on a simple key/value table.
CREATE TABLE cache (
id BIGINT unsigned not null auto_increment,
...
- 133
2
votes
3
answers
710
views
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Context
I have a table named companies_establishments that holds ~33M rows.
I created a GIN index with trigrams, so I can make LIKE queries much faster.
CREATE INDEX companies_establishments_id_index ...
- 123
0
votes
1
answer
110
views
MariaDB: Using composite primary key and index on the same columns gives strange results
Environment information:
Windows 11
10.6.8-MariaDB
In SO https://stackoverflow.com/questions/20055644/mysql-counter-within-group
I asked how can I add a counter within a group. It works quite well ...
- 257
0
votes
1
answer
55
views
Should you be liberal with create index and add to many "identifier" columns?
After reading about performance a bit in sql I notice that it is quite often suggested to add indices for foreign keys - as often data is looked up through the foreign key. Similarly PostgreSQL seems ...
- 473
0
votes
1
answer
67
views
MariaDB: defective MyISAM and ARIA Indexes after errorless import of big data file
I have a long suffering with this issue after moving forward from MySQL 5.6 and then MariaDB 10.0.
Working now on Debian 11 (Bullseye) with MariaDB "Ver 15.1 Distrib 10.9.5-MariaDB, for debian-...
- 101
2
votes
0
answers
347
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
0
votes
1
answer
941
views
How to avoid 100% CPU on CREATE INDEX
I'm indexing a partitioned table with about 200 million rows, each row is ~1kb in size.
I run multiple CREATE INDEX statements sequentially and its pegging the CPU at 100% for 2 hours. Is there a way ...
- 103
0
votes
1
answer
49
views
How to see the false positive cases with bloom index in Postgres?
I have tried to reproduce a false positive but it is almost imposible to check for an example in my database. Thats what I did.
First create the index
CREATE EXTENSION bloom;
Then create the table ...
- 1
-1
votes
1
answer
319
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
1
vote
1
answer
344
views
How can set the column as a primary key with serial type?
In my postgresql:
select index from sample;
index
---------
1
2
3
1
2
How can set the column index as a primary key with serial type for table sample?And the selected value should be as below:
select ...
- 334
1
vote
2
answers
595
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 ...
- 11
1
vote
1
answer
246
views
Which version of Stratio Lucene Index should we use with Cassandra 4.x?
We are using Stratio Lucene Indexer for V3.11.5 and currently planning to upgrade to V4.x.
As specific Cassandra Lucene index versions are targeted to specific Apache Cassandra versions.
https://...
- 185
1
vote
1
answer
117
views
InnoDB - how MySQL InnoDB pages works with underlying OS file system
I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage.
Would you mind to confirm that my understanding is correct?
InnoDB store ...
- 113
1
vote
1
answer
451
views
Oracle query taking a very long time to execute
I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical ...
- 69
0
votes
1
answer
115
views
How "trustworthy" is the cost information returned by EXPLAIN in Postgres?
I have a database with a table which is accessed with about 5 different SELECT commands. I tested each one of them with the EXPLAIN ... and EXPLAIN ANALYZE .... The database includes some data (about ...
- 125
0
votes
1
answer
1k
views
Postgresql perform sequential scan instead of index scan for indexed column
I'm currently using PostgreSQL 14.0 for windows
My target table has a 900k+ rows and my query is using single column filter (status_id).
The column status_id is indexed via btree (default)
When i ...
1
vote
1
answer
161
views
Create non clustered indexes in background without awaiting the outcome in Sql Server
I have to release a new version of an application. I created two scripts one to move the schema and one to migrate the data. The script contains a long list of queries.
The application needs to stay ...
- 303
1
vote
1
answer
116
views
Index on Column with Very Few Distinct Values
I have to query a table of stock prices that includes a column 'split_coefficient' where split_coeffieint is almost always 1.0, but very occasionally is something other than that: 2.0, 0.5, etc.
I ...
- 111
1
vote
1
answer
159
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
1
vote
1
answer
440
views
What is the way to know the number of rows a Postgres INDEX currently manages?
In Postgres, I created an index which looks like this:
CREATE INDEX foo_idx ON foo (other_id)
WHERE a = 1 AND b = 2 AND c = 3 AND other_id IS NOT NULL;
Now I'd like to know how many of the 150,000 ...
- 125
11
votes
1
answer
2k
views
If you disable a non-clustered index, are the statistics still used?
tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?
I'm aware there are many questions about "dropping vs disabling ...
- 213
3
votes
2
answers
3k
views
indexing multiple columns in a table in MySQL
I am greatly confused about creating an index for a table.
Assuming that a table users have 5 columns, id (primary key), name, email, creation date, and update date.
id | name | email | creation_date |...
- 129
2
votes
1
answer
857
views
Creating indexes in an amazon aws aurora mysql does have a cost?
I am looking for informations without finding them.
If I create indexes in a aws aurora mysql instance, do I have to pay?
The only things I found is that you pay for all I/O operations (and for ...
- 163
1
vote
1
answer
135
views
Query for MAX/MIN of non-clustered index field for partitioned table
We have a table partitioned on a two-year based hash function from the system date at the insertion time (let's call it PartitionKey). It also has a non-clustered index on a single field (Key). We ...
- 123