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.

Filter by
Sorted by
Tagged with
0
votes
1answer
92 views

Oversize non-clustered index

I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a ...
0
votes
1answer
18 views

Should I drop a btree index on a column after adding a unique constraint?

On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well. In this ...
3
votes
1answer
33 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
0
votes
2answers
52 views

Remove unused Indexes MariaDB

I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use. How ...
0
votes
1answer
42 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 ...
1
vote
0answers
34 views

Ola IndexOptimize Deadlocking On Self But No Error Record In CommandLog Table

I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is ...
0
votes
4answers
80 views

Is a Non-Clustered index needed when referencing a small table with a Foreign Key

I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time. Assume there are 2 tables, one table with a lot of ...
7
votes
6answers
558 views

SQL Server Index Drops - Considerations

I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB. I am interested in the opinions of ...
1
vote
0answers
67 views

Table(s) indexing strategy for separable data [closed]

Scenario: The data needs to be indexed by location, all of the rows include location info and also city info. I want to index by location, but there are millions of rows. The idea is that I can create ...
0
votes
2answers
42 views

PostgreSQL B-tree, good monotonic values?

I am creating a unique ID generator for my application. I am reading everywhere that monotonic keys make life easier for the b-tree. I generate an ID by combining a time part and a random part, the ...
3
votes
2answers
141 views

SQL Server Execution Plans and Index Drops

When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it? In particular I'm wondering ...
0
votes
3answers
56 views

Difference between the following postgresql indexes

On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row. The following 2 indexes, I would ...
0
votes
1answer
18 views

PostgreSQL - Removing Unused Index on Check Constraint column

I used the script from this blog to identify the unused index on my database. It returns an index which is on a column called result where I have a check constraint on that column. So removing this ...
3
votes
1answer
85 views

Why does BETWEEN use a btree index but “element is contained by” range operator (<@) does not? [duplicate]

I've got a table with a utc timestamptz which has a "btree" index on the utc column: CREATE TABLE foo(utc timestamptz) CREATE INDEX ix_foo_utc ON foo (utc); This table contains about 500 ...
0
votes
0answers
52 views

Index for finding attributes in large JSONB array

Based on this original question and the follow up question from 2015. Suppose I have a jsonb column data within a table tracks. This column holds an array with multiple keys e.g. Artist and Title in ...
0
votes
1answer
70 views

Innodb primary key advice

We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly. The existing schema has a uuid4 as a primary key, and then two additional indexes ...
0
votes
1answer
77 views

MySQL table proper indexes for performance optimization

I have a database table (created by someone else). This table consists over billions of records and records are being inserted every second or so. I need to optimize this table and hence the queries ...
1
vote
1answer
30 views

Are indexes need for parts of primary key?

In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause? CREATE TABLE my_table (...
3
votes
1answer
97 views

Execution plan changed for existing query - high CPU usage

So few weeks ago we had an alert on our production MySQL database due to a CPU usage spike. We identified the issue as a query that had been there for several months that suddenly was misbehaving. The ...
2
votes
1answer
34 views

Should partitioning increase performance of indexed accesses and updates? [duplicate]

I often hear that partitioning huge tables should significantly increase query speed and insert/update speed, because indexes are proportionally smaller. I'm struggling to understand why that should ...
0
votes
0answers
55 views

A performing monotonic UUID variant, does it make sense?

Because of supporting offline clients I unfortunately have to replace some of my integer Primary Key's for UUID's (but not necessarily the 16 byte (128-bit) RFC 4122 version). I'm on Postgres 12 (...
0
votes
0answers
18 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 ...
0
votes
0answers
35 views

Files left behind by killed `CREATE INDEX` commands?

I wrote a migration for a web application that ran CREATE INDEX as part of its deploy. When it took too long to create, our deployment system ruled it as a failed deploy, killed the deployment, and ...
0
votes
1answer
55 views

Execution plan push sort operator to avoid too many PK lookups

For a query like this: select top 40 ColumnNotInIdexCausingPKLookup from table where ColumnIncludedInIndex=A order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex Any way to ...
-1
votes
1answer
35 views

Postgresql uses slower constraint over faster index

I have a few tables (25m records +) which have a very INSERT, UPDATE and DELETE biased workload (typically 50,000 to 100,000 operations a day) where the query planner seems to make an odd choice of ...
1
vote
0answers
14 views

Query optimalization - how create proper composite index

I have mysql 5.5 and table item have around 1-3 milions rows. CREATE TABLE `item` ( `Id_item` int(11) NOT NULL DEFAULT '0', `item_Kod` varchar(15) DEFAULT NULL, `Date1` datetime DEFAULT NULL, `...
0
votes
3answers
92 views

Unix timestamp vs timestamptz index performance [closed]

I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving ...
0
votes
0answers
43 views

MySQL: message “Incorrect key file for table” “try to repair it”

I'm using MySQL 5.7.10 I have a table like this, with 100M rows and a size of 16GB . CREATE TABLE `my_table` ( `id` DOUBLE NOT NULL AUTO_INCREMENT, `entity_id` DOUBLE NOT NULL, `concept_id`...
7
votes
2answers
1k views

Do I understand indexes correctly?

I'm trying to wrap my head around the difference between various kinds of database indexes. I have created a little example for myself and I don't know whether I'm understanding everything correctly. ...
1
vote
0answers
35 views

Postgres index on timestamp column not utilized

I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column. I have a query as below SELECT * FROM employee WHERE timestamp >=...
2
votes
1answer
107 views

Save performance with large update on Index with Included Column

Points ------------------ PK QuestionId int (+30.000.000 distinct values) PK EventId int (large batches where 80.000 rows have the same EventId) Value smallint The table is about 40 ...
0
votes
3answers
98 views

How to fine tune a huge problematic Heap Table? [closed]

Problem: A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition). Facts about the problematic Table : Heap ...
-1
votes
1answer
82 views

why is mysql not using the composite index on my query?

Here is my query. select user_id, sum(user_points.points) overallpts, username from user inner join user_points on user.user_id = user_points.user_id where user_points.date >= '2020-01-01 00:00:00' ...
0
votes
1answer
52 views

Index automation job script

Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a ...
0
votes
0answers
35 views

Index not used in a remote query when values are supplied from a PL/SQL collection

I have a table with primary key column on a remote database: create table tab ( id primary key, pad ) nologging as select level id, lpad('x', 800) pad from dual connect by level <= 100000; If ...
1
vote
1answer
26 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 ...
4
votes
3answers
109 views

Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer. As ...
1
vote
1answer
50 views

Are page numbers of a book a good analogy for a clustered index? [closed]

The title pretty much covers the question. I think that the index of a book is a good analogy for a non-clustered index, as it demonstrates the extra storage and physical separation properties of a ...
1
vote
2answers
28 views

How do I add composite MySQL Indexes with denormalization and JOINS?

I have the following tables: CREATE TABLE base_event ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, created_by ... -- some columns ); CREATE TABLE transaction_events ( event_id ...
0
votes
0answers
28 views

Using GIN index for array and single value in the same key

We have table: create table last_games(id int not null primary key, name text not null, json_value jsonb); And filled with next values: insert into last_games values (1, 'Tournament1', '{"...
1
vote
1answer
153 views

How can I investigate why Sql Server is chosing the “wrong” Index?

I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes: IX_SiloId_ChangedTime_IncludeTime IX_SiloId_Time_IncludeContent I run these 2 statements ...
-1
votes
2answers
55 views

Index usage across databases [closed]

I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server. I have a newly created central database where data is replicated to from ...
1
vote
1answer
41 views

How to convince Oracle to create optimal execution plans in SQL queries with bind variables?

An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans. For example, I'd like to write SELECT like ...
4
votes
3answers
302 views

SQLServer - treat table as index

I have table that is more or less as follows: table Sample ( location bigint, device bigint, timestamp datetime, type bigint, value bigint, ) For performance reasons we also ...
0
votes
1answer
50 views

Why is this query on JSON values in SQL Server using an index scan instead of an index seek?

I have a table with the following schema: CREATE TABLE [dbo].[Obj1Json]( [ObjectID] [int] IDENTITY(1,1) NOT NULL, [PointerToSource] [nvarchar](255) NULL, [CreateDate] [datetime2](7) NOT ...
1
vote
2answers
98 views

How does a column-oriented DBMS filter on multiple columns?

I'm learning how column-oriented DBMS / "columnars" work for OLAP situations. Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to ...
1
vote
1answer
49 views

Questions on updating statistics and index maintenance jobs

Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions: For ...
1
vote
1answer
36 views

Searching for a close numeric match on spatial coordinates

I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as NUMERIC(9,6). If I run a query looking for an exact position match, ...
0
votes
2answers
39 views

Postgres: Performance Issue: Query on enormous data fails to use index

This is the schema of task_statuses table Table "public.task_statuses" Column | Type | Collation | Nullable | Default ---------------...
7
votes
2answers
209 views

Clustered Index fragmentation vs Index with Included columns fragmentation

I have a relative large table (for me) with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign). Tip -------------- Id int (...

1
2
3 4 5
57