A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [index-tuning]

The process of determining which indexes are useful and which are not.

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

Should a filtered index theoretically perform faster in this example?

I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown". It also has columns called "...
0
votes
0answers
28 views

Will a composite index containing an enum with few choices or a boolean increase or slow down query speed? [closed]

I have the following query: SELECT new DTOs.EntityDTOs.cropvariety.SeedCalendarResultDTO(u.id, u.crop, i.previewImageUrl, u.plantType, u.growthTemperature, u.hardiness, u.growthTime) FROM SeedRecord ...
0
votes
1answer
58 views

Non-clustered index covering a clustered index

I have a table that has the following indexes: IXC clustered index that includes a single column : A asc. IX1 that includes two columns : A asc, B asc. In this case ICX seems like a duplicate index ...
1
vote
0answers
49 views

Oracle Context Index - finding unsynchronised records

Context indexes can be update with the procedure: Ctx_Ddl.Sync_Index (index_name); But what exactly is synchronised by this function? How does Oracle know what is unsynchronised (i.e. how does it ...
0
votes
3answers
55 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
-1
votes
3answers
52 views

ID, UUID and “deleted_at” columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
1
vote
1answer
41 views

Postgres: How can I force index usage for view with latest rates?

We import currency rates into DB: CREATE TABLE currency_rate ( id int8 NOT NULL, date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(12,6) NOT NULL, CONSTRAINT ...
2
votes
1answer
56 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
0
votes
2answers
56 views

non clustered index coverage with INCLUDED columns

If I have two indexes: IDX_1 = (COL1), (COL2) DESC IDX_2 = (COL1) INCLUDE (COL3) will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE ...
1
vote
1answer
24 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
0
votes
1answer
54 views

Can I drop an overlapping index if the larger index is unique

I have 2 nonclustered indexes: IX1 = [col1] IX2 = [col1],[col2] (unique). I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied ...
1
vote
1answer
32 views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
1
vote
3answers
69 views

When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

When applicable, will using the UNIQUE constraint for a clustered index help improve performance in any way under any scenario?
0
votes
1answer
38 views

What makes for a good index in this scenario?

I have a pretty big temp table (~5 million rows) that I need to update a few fields on. Here's an example update statement: UPDATE T SET CarModel = CASE WHEN CarModel = '350z' AND ...
0
votes
1answer
57 views

Why is this datetime index not being used?

I have a table like this: CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [...
2
votes
1answer
23 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
0
votes
1answer
31 views

Mysql - query optimization

I have a DB schema as such, SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='...
1
vote
1answer
70 views

Indexing Strategy for the query performance

I have a question on indexing strategy as i am trying to do indexing for a database. I understand the basic indexing strategy which are: Clustered index on PrimaryKey Add Non Clustered index for ...
0
votes
1answer
71 views

Postgres text column too large for an index - searching for IP address, so I don't think text search will work

I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text. I need to do a query WHERE ...
0
votes
1answer
76 views

Worse query performance even after improving the query by making it to run in parallel mode

I have a SQL Query which gets generated from application using EFF. It uses a view and this view has joins to multiple tables in there. The query runs slow and when i looked at the execution plan,i ...
1
vote
1answer
85 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
0
votes
1answer
66 views

Determine least number of columns required for unique (clustered) index

I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit. I was looking at a certain table and noticed that the ...
-3
votes
1answer
43 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
5
votes
3answers
209 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
1
vote
1answer
42 views

How to make Postgres Multi-Column and Multi-Table Search More Efficient

I have a Shipment table with some basic data about a shipment and a ShipmentItem table which contains additional attributes about that shipment with a foreignKey on the Shipment table's primaryKey. ...
7
votes
2answers
406 views

Force SQL Server to use fragmented indexes?

I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
4
votes
1answer
125 views

Add INCLUDE term to primary key index in Postgres 11

I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra ...
1
vote
1answer
68 views

Is it useful to index a column if it is already the first column in a composite index? [closed]

Suppose I have this table (simplified from my project): create table BillTax ( BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID), TaxCode varchar(20) not null,...
1
vote
1answer
39 views

Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?

I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database. Most of the tables in the database at present ...
-2
votes
1answer
32 views

SQL server index autotuning in Azure

I know SQL server index autotuning available in Azure DB. Which Azure DB version does this work with? Also does this work for AWS SQLdb? Thanks for your help.
0
votes
1answer
32 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
4
votes
1answer
77 views

Delete unused indexes from Microsoft Dynamics CRM Application

So I found tons of unused indexes from our CRM application with this query from Pinal: SELECT o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , ...
0
votes
0answers
69 views

Reproduce Deadlock in SQL Server

I have identified a deadlock by using Solarwinds, I have noticed that it is happening in a specific table, that table only has a primary key, which tells me that the deadlock makes sense since every ...
14
votes
4answers
1k views

Index Uniqueness Overhead

I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
0
votes
1answer
70 views

Benefit to additional index with key column moved to includes

Say I have an Index Foo with: Keys [A, B, C] and Included columns (D, E) Is there any performance reason to have an additional index Bar with: Keys [A, B] and Included columns (C, D, E) My ...
0
votes
0answers
96 views

MongoDB date range index bounds

I have a mongodb (V3.6.10) collection with the following stats&indexes: { "ns" : "RepoProd.raiding", "size" : 145312567936, "count" : 6776728, "avgObjSize" : 21442, "...
0
votes
1answer
64 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
1
vote
0answers
55 views

Finding which queries used an index

I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server. I ...
0
votes
2answers
41 views

No index used warning in WHERE column IS NULL

I'm getting this warning: No index used in query/prepared statement SELECT * FROM mTable WHERE my_column IS NULL But the my_column is already indexed. MariaDB [mytest]> SHOW INDEX FROM mTable; ...
0
votes
1answer
33 views

Indexing every WHERE column?

I tried to turn on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me: No index used in query/prepared statement... in some of my app's queries. Is it really best practice to index ...
0
votes
1answer
77 views

Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?

I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
1
vote
2answers
37 views

PostgreSQL partial index not used depending on boolean comparison

I need help understanding why a partial index would be used in one query and would not in second one which is effectively the same. I have the following table: Column | Type ...
0
votes
0answers
29 views

compund MongoDB index with _id

I have database with few fields: _id, field0, field1, field2, field3, field4, field5.. I created index on as follows: db.usertable.createIndex( { _id: 1, field1:1} ) The expected that a query ...
3
votes
0answers
33 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
1
vote
0answers
60 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
3
votes
2answers
89 views

Database design that requires an index over two tables

Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products ...
1
vote
1answer
25 views

Azure SQL Server - Cannot get rid of the sort operator

I'm using Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 3 2019 13:27:00 Copyright (C) 2019 Microsoft Corporation), Premium tier 4 (500 DTUs). Query: offer progress has a history ...
2
votes
3answers
289 views

Why does sql server prefer the nonclustered index over the clustered index?

I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However,...
1
vote
0answers
35 views

Spatial index unused on nested set

I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any ...
0
votes
2answers
139 views

How to index/optimize sum of two columns in PostgreSQL?

We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background ...