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
1 answer
14 views

Speed up Postgres Query and Function

I have a tables that looks like this: create_table "cboe_raws", force: :cascade do |t| t.date "quote_date", null: false t.string "underlying_symbol", null: false t.string ...
user avatar
1 vote
1 answer
13 views

How to query dates in different timezones?

I have a table and index in a PostgreSQL 10.18 database: CREATE TABLE some_table ( expires_at timestamptz ); CREATE INDEX ON some_table(expires_at); Is there a way to write this query in a way to ...
user avatar
  • 163
0 votes
1 answer
18 views

Select function-based index data as rows in a query?

I'm trying to learn about indexes in Oracle 18c. As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index: create index ...
user avatar
  • 1,375
0 votes
1 answer
25 views

How do I find out why CREATE INDEX CONCURRENTLY is failing?

I set off the creation of an index with the command CREATE INDEX CONCURRENTLY index_Name ON namespace.table_name(column_name); and watched the action using this command on another session: select now(...
user avatar
  • 101
1 vote
2 answers
39 views

Why is the query planner only using one column of two in the index?

I have a table on MySQL 5.7 with the following definition and indexes: +-------------------------+---------------+------+-----+---------+----------------+ | Field | Type | ...
user avatar
  • 129
2 votes
2 answers
176 views

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, ...
user avatar
  • 599
0 votes
2 answers
27 views

How to enforce only one NULL value per unique combination of some other columns?

here's the situation, I have a table A with like such CREATE TABLE A ( id UUID NOT NULL PRIMARY KEY, B_id bigint NOT NULL, C_id bigint NOT NULL, some_value ...
user avatar
0 votes
1 answer
41 views

Should I be using a non-clustered primary key on multiple columns?

I have a very large table that will just continue to get larger. It contains hourly interval meter usage data for around 22,000 meters. So, everyday 22,000 * 24 hours = 528,000 records are created. ...
user avatar
  • 1
0 votes
0 answers
18 views

Indexing a pivot table. Is really necessary?

I'm creating a small app for a contractor where an Event is assisted by multiple Clients. ┌─────────┐ ┌───────────┐ ┌────────┐ │ Event ◄──────► Assitance ◄──────► Client │ └─────────┘ ...
user avatar
0 votes
2 answers
55 views

The implications of lack on indexes on supporting a large production database

The situation is as follows: I started at a new company several months ago in a support capacity. Part of my role will involve running queries in production to help diagnose application issues. I was ...
user avatar
0 votes
1 answer
24 views

Check how many Bytes does my index need to be stored?

Let's suppose we have this table: CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER PRIMARY KEY , C_NAME CHAR (25), C_ADDRESS CHAR (40), C_NATIONKEY INTEGER REFERENCES NATION(N_NATIONKEY), C_PHONE CHAR (15),...
user avatar
  • 95
0 votes
1 answer
24 views

Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table

I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with: ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine I'm finding the ...
user avatar
  • 3
2 votes
2 answers
293 views

how to find out the `Key Lookups` in the current query plan?

I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
user avatar
0 votes
1 answer
35 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 ...
user avatar
1 vote
1 answer
47 views

Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)

Hope you're doing good. I've recently moved from Amazon RDS to my own PostgreSQL instance in Hetzner (in order to colocate it with the server and reduce the roundtrip). However I think that some ...
user avatar
  • 11
0 votes
3 answers
63 views

The UPDATE queries is 50x slower than its SELECT query

The UPDATE queries is 50x slower than its SELECT query. I have a table named sync_read and the have the following columns id club_id created_at user_id queue_id The table have the following Indexes: ...
user avatar
0 votes
1 answer
46 views

Forcing a sequential scan over the index scan

I have this query select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where p_partkey=ps_partkey and s_suppkey=...
user avatar
  • 95
1 vote
1 answer
77 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 ...
user avatar
0 votes
0 answers
28 views

Postgresql 14 Primary Key not working, allows duplicates

I discovered that my 3-column (integers, not null) primary key is allowing duplicates! Then I created new unique index and replace the current one, and the problem still exists, how is that even ...
user avatar
  • 109
1 vote
1 answer
28 views

New Index creation can benefit from existing Index

I was wondering if a new index and consequentially its creation might benefit from an existing index. It is something just out of curiosity but extremely helpful in the case when you have to create a ...
user avatar
1 vote
2 answers
28 views

how to properly index a varchar column that won't use LIKE

I've been experimenting with EXPLAIN and noticed that the row count was big for our query so I tried adding an index to the VARCHAR column. First I added FULLTEXT but it ended up just making the query ...
user avatar
1 vote
0 answers
14 views

Mariadb 10.5 FORCE INDEX can't be used with the PRIMARY key?

In a InnoDB table I can use any index with the FORCE INDEX or USE INDEX except for the PRIMARY one. (This is on RDS, if that matters) ERROR 1176 (42000): Key 'id' doesn't exist in table 'mytable' ...
user avatar
1 vote
0 answers
39 views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
user avatar
0 votes
1 answer
21 views

Index rebuild/reorganize fragmentation percentage mean the internal (inside the page) or external (amongst pages)fragmentation?

Fragmentation of 5% - reorganize Fragmentation of 30% - rebuild Index rebuild/reorganize fragmentation percentage mean the internal (inside the page) or external (amongst pages)fragmentation?
user avatar
  • 2,231
-1 votes
2 answers
23 views

What other than `delete and re-creation of the index` happens when an index is rebuilt which does not happen in the reorganize command?

When an index reorganize command is fired, then it will not drop and re-create the index. However just like a rebuild it will defragment the data and ensure that the physical ordering of the page is ...
user avatar
  • 2,231
0 votes
1 answer
42 views

Optimize join without denormalization

I have two tables: orders(order_id, scan_timestamp) and order_products(order_product_id, order_id, pick_number, quantity) and I want to know how many of a particular product (identified by ...
user avatar
  • 415
2 votes
1 answer
29 views

Index not used for date range condition

I was querying like this: SELECT count(*) FROM orders WHERE planned_shipping_date >= '2022-04-04' AND planned_shipping_date < '2022-04-05' Then I came across this answer and, because in more ...
user avatar
  • 415
2 votes
1 answer
324 views

PostgreSQL 9.6 - ORDER BY timestamp DESC LIMIT 10 is extremely slow even with index

I am facing some curious cases in my postgres 9.6, mainly for queries that follow the format: SELECT a.* FROM a INNER JOIN b ON a.id = b.a_id WHERE a.field1 = '' AND b.field2 > .. ORDER BY a....
user avatar
  • 121
0 votes
1 answer
26 views

Unique constraint replaces existing foreign key

I have two tables, parent and child, the child table has a FK on the parent table using the parent_id column. When adding this FK I can see that the child table has both the FK and an index to ...
user avatar
  • 151
5 votes
2 answers
244 views

dateadd in the where clause - how can I avoid a full scan in this particular situation?

I have the following query: SELECT u.userId, app.applicationId FROM app.application AS app INNER JOIN app.applicant AS ap ON ...
user avatar
0 votes
0 answers
23 views

PostgreSQL cache function return value

Suppose I have defined a function that is expensive to compute. Is there a way to define an index in PostgreSQL to cache the return value for a given set of parameters? For example, let's say that I ...
user avatar
0 votes
0 answers
29 views

Oracle 19c-ee CTX_REPORT.INDEX_SIZE and CTX_REPORT.INDEX_STATS cannot be executed

We created a simple search index, based on a 19c-ee instance of the official Oracle Docker images, with the following script: CREATE TABLE EVT ( ID VARCHAR2(36) NOT NULL, EVENT BLOB NOT ...
user avatar
  • 11
0 votes
2 answers
44 views

What does non clustered index points to when the table has clustered index that is not unique?

Assume table has name and id columns. ID is unique non clustered index. So this points to the heap table. If I set the Name as clustered index (non-unique), then what will the ID non clustered index ...
user avatar
  • 2,231
0 votes
1 answer
21 views

Use composite index or index merge to support searching variable columns?

I'm trying to understand how to best add indexes to the following table: CREATE TABLE ideas ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, idea TEXT NOT NULL, age_range VARCHAR(30) NULL, ...
user avatar
0 votes
0 answers
81 views

MySQL 8 falsely giving "column x cannot be null" error on update

I'm running the below query on a MySQL 8 managed server hosted at DigitalOcean! UPDATE games_codes SET result=5 WHERE id=1; When I run this query, I'm getting the following error. Column field ...
user avatar
1 vote
1 answer
60 views

Optimize performance of nested GROUP BY

I have a query on the table random_numbers with a GROUP BY nested inside another GROUP BY: SELECT a, min(x_max) AS x_minmax FROM ( SELECT a, b, max(x) AS x_max ...
user avatar
  • 31
2 votes
2 answers
48 views

does a btree index on an array field actually do anything?

In PG you can create a table like CREATE TABLE foo ( id uuid PRIMARY KEY DEFAULT gen_random_uuid (), name text NOT NULL, things text[] NOT NULL DEFAULT ARRAY[] ::text[] ) and a related index ...
user avatar
  • 131
0 votes
0 answers
20 views

primary index on column having duplicates

must primary index be done over the primary key column? can the primary index be done on a column having duplicates? I searched the textbook but I found it not that clear?
user avatar
1 vote
1 answer
57 views

How can I read a EXPLAIN ANALYZE in PostgreSQL

I have read a bunch of resources on the internet and the official documentation of PostgreSQL and I still don't know how to interpret the result of a EXPLAIN ANALYZE. I'm using PostgreSQL 12.8 and I ...
user avatar
  • 113
3 votes
1 answer
54 views

Why does an index on the join predicate does not improve the performance of a hash join?

Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join. However, couldn't a B+ tree index ...
user avatar
  • 33
3 votes
2 answers
304 views

Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?

we have a table with 4 columns ( example ): create TABLE [dbo].[myTable] ( [Id] BIGINT NOT NULL, [Id2] SMALLINT NOT NULL, [Id3] SMALLINT NOT NULL, [IdUnique] ...
user avatar
  • 7,100
0 votes
1 answer
18 views

Using Indexing When Performing JOINs on Part of a Composite Key

I have a table in my database (hosted on MariaDB) that looks like this (I have given the definition in SQLAlchemy), class Address(Base): __tablename__ = 'address' __table_args__ = {'schema': DB_NAME} ...
user avatar
0 votes
1 answer
36 views

Do I need to index a column that is used in every query, but rarely deviates from the default?

A very practical example: Assume an User table that has a "deleted" column that stores timestamp. Assume that there is a "deleted IS NOT NULL" filter on practically every query ...
user avatar
  • 197
2 votes
2 answers
100 views

Create unique index based on two columns (bi-directional)

I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns. Thinking about ...
user avatar
  • 123
2 votes
2 answers
26 views

Index Collision in UTF8 on MariaDB

I am having a problem moving (converting) a DB from SQLite3 to MariaDB 10.3.29 server running on a Synology NAS. One table, actor, has 3 columns actor_id name art_urls Some actors have 2 entries in ...
user avatar
-6 votes
1 answer
30 views

cursor syntax to fetch and drop tables in the db

I have a list of tables from a select query which gives me at least 50 tables daily . Need to drop them as they are temporary. I need a cursor to fetch and drop each one of them in a loop. I am using ...
user avatar
0 votes
3 answers
31 views

Constraint to force pair of IDs

I would like a constraint to force a pair of IDs. For example, I have a table with two columns. car_id tire_id However, a specific car_id must always be paired with the same tired_id. Meaning a car ...
user avatar
  • 151
1 vote
3 answers
74 views

Does adding a non clustered index to foreign key speed up the join?

I have 2 large tables. Sample query: SELECT USERNAME,S.ProductName,S.Amt,S.Date FROM USERS U JOIN SALES S ON U.ID=S.UID Presently the Users.ID and Sales.ID are clustered indexes. So as to speed up ...
user avatar
  • 2,231
1 vote
2 answers
60 views

What is the fastest way to change a clustered index on a 64GB table?

We have a table (64 GB in size, 256795926 rows) which has a very bad PK (clustered) index. Currently it's a varchar(99) column (average size 50 characters). There is already a bigint identity column, ...
user avatar
0 votes
1 answer
19 views

What is this mongo index with field._ref.$id_1

I have a document in a Mongo collection which has an index as follows: field._ref.$id_1 This is tripping up a new database driver we're planning on using - it doesn't seem to understand this format, ...
user avatar

1
2 3 4 5
61