Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
115 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. ...
6
votes
1answer
1k views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
7
votes
2answers
423 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 ...
5
votes
2answers
8k views

where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ? it will stay with that table and add a new ...
4
votes
1answer
231 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
78 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
50 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 ...
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 ...
-2
votes
1answer
33 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.
4
votes
1answer
126 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
76 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
73 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 ...
7
votes
3answers
4k views

Finding stored procedures with missing indexes

How to scan for stored proc that need indexes to be created. Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
1
vote
0answers
69 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
54 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
36 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
123 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
1answer
232 views

Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH?

Given the following: Target platform: MySQL 5.7, using InnoDB. Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by ...
1
vote
2answers
73 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 ...
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` ...
2
votes
1answer
63 views

Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified?

I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query: select i.table_name, i.index_name, i.rows_per_key, coalesce(t.rows_read, 0) as ...
3
votes
1answer
30 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
8
votes
2answers
847 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
5
votes
2answers
5k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
4
votes
2answers
495 views

Composite index

Assuming I have a table X with 12 columns. In my queries I am filtering on these conditions: select * from x where a=@a and b=@b select * from x where a=@b and b=@b and c=@c select * from x where ...
4
votes
1answer
92 views

Covering Index Question

I have the following query Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED, PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND, CASE Left(PT.INVENTREFID, 3) WHEN '...
4
votes
1answer
6k views

Ola Hallengren Index Optimise Script not Rebuilding Indexes [closed]

I have downloaded Ola Hallengren's script and deployed to master database. I run it using the below... EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
3
votes
2answers
91 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 ...
4
votes
3answers
529 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
1answer
26 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 ...
1
vote
0answers
43 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 ...
2
votes
1answer
2k views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
0
votes
2answers
302 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 ...
1
vote
2answers
230 views

What is the best practice when an index with key/include columns are inversed for a second index on the same table

I have an index on a table of the form: NONCLUSTERED INDEX [IDX1] ON [TABLE] ( [COL_ID_A] ASC ) INCLUDE ( [COL_ID_B] ) For a different query on the same table MSSMS proposes the following ...
1
vote
1answer
95 views

Query Index question

I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the ...
7
votes
2answers
4k views

Outer Join suppressing Index usage?

I have a client program that is executing a query against a View that outer joins one table to another. The performance is bad and I have been trying to tune it by adding the right index. The query ...
0
votes
1answer
461 views

Is MongoDB index creation single threaded?

I have rather big dataset - 2TB of text data, 18.000 millions of lines. Each document is same: 5 fields: 4 strings + 1 integer. All fields should be indexed and searched by. That is: array of 3 ...
1
vote
0answers
61 views

Background index creation resource provisioning

I have a long running background index creation running in MongoDB. However, the CPU and RAM usage is below 30%. Is there a way to allocate more resources to the index creation process or is it ...
0
votes
1answer
122 views

Optimizer choosing a bad index

I have a pretty simple select statement, select column 4 from table_x where column1=:1 and column2=:2 and column3=:3 Columns 1,2 and 3 make up the primary key on the table. For some reason, ...
2
votes
3answers
80 views

Identify proper index for group and order by date and hour (2 columns)

PostgreSQL 10.4 I have a table that has already an index on date column. Current query plan is doing a Bitmap Heap Scan using the existing date index. I would like to add a new index for this query, ...
3
votes
1answer
139 views

Ways to Reduce Database Size and possibly improve performace. Untouched enviroment

I am new to an environment. The databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance. Unfortunately, I only have Web edition ...
1
vote
1answer
314 views

Index Optimization for Datetime comparing day of week and hour

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
4
votes
1answer
437 views

Redundant indexes in SQL Server

I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce ...
3
votes
1answer
177 views

What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?

Question: Is there a better indexing strategy or query SELECT that I can use for looking up one large data set against another large data set? Or, should I look at placing the lookup dimension table ...
5
votes
2answers
143 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
0
votes
2answers
172 views

SQL Alter Index within a Script

I posted this in StackExchange and it was mentioned that it should be moved to this exchange. So here it is: I've been reading about SQL Index Fragmentation and how to rebuild/reorganize. I'm curious ...
2
votes
2answers
1k views

Can Postgres use an index-only scan for this query with joined tables?

This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns? Consider the inverse of the schema in the other question where you filter in the joined-on table: CREATE TABLE ...
1
vote
1answer
54 views

Advice on fine-tuning query and index for performance

Currently my query is as below: select id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.status =0 AND NOT exists (...
1
vote
2answers
237 views

PostgreSQL: hstore trgm search on fixed key

I have a table of products with custom properties for each product. So I have props::hstore column in my table. I want the user to be able to search products by key/value by first selecting the key ...

1 2
3
4 5
14