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
1
vote
1answer
12 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 ...
1
vote
1answer
39 views

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

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

MySQL error: Missing index for constraint, can someone help me pls? [closed]

CREATE TABLE Account_Base (ACCT_ID INT(4) NOT NULL, OPEN_DATE DATE NOT NULL, CLSE_DATE DATE NOT NULL, PDCT_TYPE_C VARCHAR (10), OPEN_CHNL VARCHAR (100), ASST_OPEN_CHNL_DETL VARCHAR (100), ...
0
votes
0answers
12 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
99 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
53 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
28 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
292 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
47 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
66 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 ...
2
votes
1answer
38 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 ...
2
votes
1answer
31 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
34 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
199 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
votes
2answers
46 views

Rebuild Indexes [duplicate]

We have a database named WSS_Us_App in our Production SQL Server 2014. In this database all of the indexes need to be rebuilt as most have a fragmentation level greater than 50%. I plan to rebuild ...
0
votes
1answer
31 views

what's the best way to execute fast aggregation queries based on YEAR and MONTH filters in addition to dynamic filters

My SQL table is going to have millions of rows. Currently, with only 2M rows, my aggregation queries take anywhere between 20 seconds to a minute. The filters in WHERE clause for aggregation queries ...
1
vote
3answers
79 views

Find filtered indexes on an Oracle table

How can I find if an index is filtered? Filtered indexes (borrowing the term from SQL Server, or known as "partial indexes" in PostgreSQL) can have very high performance when they focus on ...
0
votes
1answer
26 views

If i index two columns (indepentently) do i get the same performance as putting and index on the two

I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used. From my understanding, if you had a BTREE index on each ...
0
votes
3answers
53 views

Optimal Indexing Strategy

For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the ...
0
votes
1answer
26 views

Postgres - indexing multi-tenant database

Our multi-tenant application uses Postgres database. The application was created many years ago, and now we are seeing some performance issues when load increases. Every database table we have has at ...
0
votes
1answer
66 views

Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? [closed]

I've used Ola hallengreens IndexOptimization script first. But after every update of our program which changes a lot of data at once the log file increases so much that the log partition (512 GB) runs ...
7
votes
4answers
217 views

Using a filtered index when setting a variable

I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This ...
1
vote
2answers
27 views

How are entries in index leaf node ordered?

I am reading the book 'SQL performance explained' and when talking about indeces, it says that databases use doubly linked lists to connect index leaf nodes. Each node is stored in a database block ...
5
votes
1answer
179 views

Indexed view not used in actual execution plan?

I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table: CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING AS SELECT [...
-1
votes
1answer
22 views

Oracle can not find a record with a specific field

In our application we face an weird situation, consider a table name TAX exists in the database, when I query it with select * from tax where fk_pay = 123456 a record is returned with id = 654321 but ...
1
vote
1answer
49 views

Index on a Computed Column(using udf)

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for ...
0
votes
3answers
20 views

Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

I have 3 enum() columns with the same option values inside. I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that ...
1
vote
2answers
55 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','...
5
votes
3answers
516 views

Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS?

The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table ...
0
votes
1answer
27 views

Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table

I'm on SQL Server 2019 Enterprise edition. I have a table with this definition CREATE TABLE dbo.MyMemoryTable ( FirstId int NOT NULL, SecondId int NOT NULL, MyCount int NOT NULL, ...
0
votes
1answer
51 views

Do triggers cause extra index updates?

I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case. Long story short they are never setting nullable fields to null. They are putting in ...
1
vote
1answer
51 views

INSERT with ON CONFLICT does not insert rows as expected

We have two tables: CREATE TABLE listing_master ( id SERIAL PRIMARY KEY, feed_id character varying(20) NOT NULL, feed_listing_id character varying(20) NOT NULL, updated_time timestamp(...
0
votes
2answers
55 views

Postgres REINDEX time estimate

I've got an older DB (postgres 10.15) that's not yet been upgraded. One problematic table had a few large indexes on it, some of which were corrupt and needed reindexing. Since it's not on version 12+,...
0
votes
1answer
29 views

Sql Server Dynamic partition with page compressed index

I created a table with partitions by following this 2012 partitioned index document. I created partitions monthly based on Date_Id column; CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT) AS RANGE ...
0
votes
2answers
62 views

Clustered index in ETL (drop - insert - create?)

I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is ...
6
votes
2answers
211 views

Identifying Unused Indexes on SQL Azure

I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information. See: https://...
0
votes
2answers
57 views

Need advise on Indexaphobia: High value missing index with High Impact. When index is already there

I am trying to understand the true meaning of Missing index for this particular table when I already have index on it and how to address it. I have a heavily used table and is of approximately 2.5GB. ...
0
votes
1answer
19 views

Can Postgresql exploit “accidental” clustering in plans?

Suppose I have an append-only table with columns customer_id (randomly generated string) and x, and lookups are always done on customer_id. Say the data look like below, as if we get a batch of rows ...
0
votes
1answer
30 views

Disk space efficient foreign key index for insert intensive scientific database?

I'm working tuning a scientific database whose associated simulation is very insert intensive (i.e., run for a long time inserting data, execute summary query at the end). One of the tables is ...
0
votes
1answer
74 views

Understanding about SQL index on DateTime Column

I have about 1 million records. When searching through the date range, it is surprisingly slow. Then I do the google search and someone suggested to do the index on the "DateTime" column. I ...
-1
votes
2answers
60 views

MySQL 8 - optimize all tables + indexes

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
0
votes
2answers
43 views

MongoDB covered query needs to fetch and examine documents when exists operator is used

I need to execute following query on mongo db: db.testCollection.find({ dataType: { $exists: true } }, { _id: 0, date: 1 }) .sort({ date: -1 }) .limit(1) so basically only I need to do is to ...
2
votes
1answer
67 views

What type of locks are needed when creating a Postgres index concurrently?

I know that when an index is being created concurrently, only a SHARE ShareUpdateExclusiveLock lock is needed. But is there a more strict type of lock needed at the very start of the process for a ...
1
vote
1answer
51 views

Does PostgreSQL support wildcard indexes on JSON?

I have a multi-tenant service with a table like this: project_id | user_id | user_properties Each project belongs to a different customer and customers can freely attach metadata to their users. A ...
2
votes
1answer
17 views

Why MySQL pick a different index for the same query structure but different values?

Right now i have some issues understanding how MySQL picks the index to use on a query. I am executing a "COUNT QUERY", but everytime i use a different foreign key for the query, MySQL picks ...
1
vote
1answer
25 views

Why can't I insert an email on a table that has one more dot than an existing one? [closed]

I'm using the official Heroku add-on for PostgreSQL and I have a database running on version 12.4 of PostgreSQL. I'm facing an issue where on my users table I have an email like this fakeemail@domain....
1
vote
2answers
35 views

How to index range condition and ordering by a different colum?

I use PostgreSQL and i have a table like this: create table transactions ( uuid uuid, user_id integer, created_at timestamp, amount numeric ); create index idx_transactions_user_id_created_at ...
0
votes
1answer
13 views

Should a meta_key or other key-value table columns use a HASH or BTREE index?

I've noticed that my WordPress database has BTREE indexes everywhere. By my understanding, BTREE indexes allow more efficient < > and BETWEEN queries, but are otherwise less efficient than a ...
2
votes
3answers
89 views

Varbinary startswith query using index

I've got this tree-like key (think object OIDs--it's very similar) to store and index in a table, for which queries want to select subtrees thereof. The most straightforward way to do this on a btree ...

1
2 3 4 5
56