Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
652
questions
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 ...
