Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
238 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 ...
6
votes
1answer
383 views

Are two indexes needed?

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter. Which indexes should I use? Should I have two ...
7
votes
1answer
406 views

Index help for bad Query on fairly big table (2m)

I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records). I have been trying to get the indexes working efficiently. There are a few other ...
-1
votes
2answers
180 views

Queries key look up [closed]

SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col25... FROM Tabl1 WHERE ( Col30 IS NOT NULL AND Col28 <> @P0 AND DATEDIFF( dd, Col31, ...
0
votes
0answers
49 views

Slow query with order by join table

I have a SQL with multiple join left, because some data is empty. However, I need to sort the data using a string column from a join table. I thought about creating an index for this table, but this ...
0
votes
1answer
87 views

SQL Server indexes Work Sometimes [duplicate]

So I have the following indexes on my table: CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link] ( [Company] ASC, [Record ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
2
votes
3answers
452 views

Struggling to create an efficient index for this query

I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of ...
1
vote
2answers
294 views

No missing index requests on SQL Server 2012

I am trying to optimize the database by creating missing indexes, but the dm_db_missing_index_details table does not have any data. I have the same database on different server and there are many ...
-1
votes
1answer
71 views

Composite index and Normal index,redundant/duplicate index - Incurred Performance penalty [closed]

Need suggestion on below indexes, whether our composite index is enough and can the normal index be removed. Scenario - We have ix4 and ix6 indexes as below, please review and advise if ix4 is ...
2
votes
2answers
218 views

Enlighten me about INCLUDE on indexes

Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES: He gives some examples and etc. about INCLUDE, but I'm confused about ...
1
vote
0answers
895 views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
0
votes
1answer
131 views

Why are bulk multi-column key queries so slow in MySQL?

(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance) Consider the following schema: Table T: col0: the usual autoincrement primary key col1: varchar ...
2
votes
1answer
1k views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
2
votes
0answers
97 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
1
vote
1answer
90 views

Is there any benefit to having both an ordered and unordered index on the same column in postgres?

I have inherited a postgres database where there are two identical indexes except for their order: CREATE INDEX avail_time_ix ON table (availability_time); CREATE INDEX avail_time_dc_ix ON table (...
3
votes
2answers
2k views

Mysql Index for concat select

For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry date,last_entry,...
0
votes
1answer
3k views

MySQL: How index speed up query execution

I have read multiple articles about MySQL indexes. Some of the important pages are below: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html https://stackoverflow.com/questions/3567981/how-do-...
1
vote
1answer
1k views

Using composite indexes with date ranges

I have a table of rules that are activated depending on the date. id - name - value - start_date - end_date 1 - ABC - 10 - 2015-12-01 - 2015-12-31 2 - DEF - 15 - 2016-02-01 - 2016-02-29 My ...
2
votes
1answer
62 views

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

I have the following tables: CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'draft', `publish_at` datetime DEFAULT NULL, ...
1
vote
2answers
138 views

Live update index in production database [closed]

I have read Perform Index Operations Online provided by MSFT but I couldn't find any detail information for my questions. My questions are: What is the impact when I update (create/drop) non-...
36
votes
1answer
28k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
1
vote
1answer
104 views

Include Columns in Index : SQL Server

I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does ...
5
votes
2answers
6k views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
1
vote
3answers
88 views

Performance tuning across two tables using indexes?

Let say I have two tables and a SQL query like this SELECT table_a.*, table_b.* FROM table_a, table_b WHERE table_a.id = ? AND table_b.col_x = table_a.col_x AND table_b.id = table_a.id; Would there ...
5
votes
3answers
6k views

Does it make sense to create index with all columns included

We have a table with columns ID OtherTableId DataA DataB DataC DataD Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or ...
3
votes
3answers
190 views

Is there any way to test which clustered index is better for specific queries?

We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we ...
0
votes
2answers
52 views

Optimizing the table to hold a million records

I want to use a table with a column that should have a million records and it has to retrieve the results very fast. Im going to use it in a query like below Database : MySQL select tags from ...
2
votes
1answer
85 views

Efficient predictable ordering in PostgreSQL

I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I ...
1
vote
3answers
374 views

How to remove multiple redundant indexes?

I have a legacy app. I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 ...
0
votes
1answer
123 views

Composite index design where only a subset will be queried

I have a table with columns A (typedate), B1 ... B24 (all boolean). Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will ...
0
votes
1answer
116 views

use DBCC SHRINKDATABASE after rebuild / reindex [duplicate]

I have little free disk space. Why shouldn't you use DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) after every rebuild/reindex operation?
0
votes
1answer
31 views

Primary index is being used, not the one which is on the field present in the where condition

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column. cat_id (primary key), acc_id, islive Another table is cat_brand table which has below fields ...
4
votes
3answers
5k views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
0
votes
1answer
283 views

SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex

I observed blocking and time outs in one of our production server and there is prod outage to one of the application continuously, When i run sp_BlitzIndex i got Aggressive Over-Indexing, ran script ...
1
vote
1answer
736 views

Index by subkey inside subkey inside array in jsonb

I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example: d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ] f492b26d-...
0
votes
1answer
488 views

Fetching index avg fragmentation for each database

i am beginner and writing a query to fetch avg fragmentation in percent to rebuild indexex for db i used cursor but i m able to bring for one database so how to include for all database using outer ...
-1
votes
1answer
57 views

Adding Multiple Individual Indexes vs One Combined Index for Multiple Foreign Keys

Background I have a "join" table that is used to join one "transactional" table with a "category" table based on multiple columns (and through several joins): TxnTable(Entity1Name, Entity2Name, ...
3
votes
0answers
222 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
0
votes
1answer
33 views

I need help creating an index for timestamp

I have a query taking too long. This is the table: mysql> describe parking_data; +----------------+--------------+------+-----+-------------------+----------------+ | Field | Type ...
1
vote
1answer
222 views

Performance Tuning ETL

I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then ...
1
vote
0answers
515 views

mongodb aggregation query performance

We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse. The record count is about ~4m for PurchaseOrder and each purchase ...
0
votes
1answer
197 views

How to improve execution time for queries by multiple fields of different data types on a single table?

Table design On SQL Server Microsoft SQL Server Enterprise Evaluation (64-bit), version 12.0.5207.0, I have this table, called InputResult: Table has 11.645.408 rows. The columns ValueAsString, ...
46
votes
2answers
45k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
19
votes
1answer
2k views

What factors go into an Indexed View's Clustered Index being selected?

Briefly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
10
votes
1answer
6k views

Difference between clustered index seek and non clustered index seek

What is the difference between Clustered Index (CI) seek and Non Clustered Index (NCI) seek? Does one perform better than the other? The reason I am asking this is because I have a table with 50 ...
1
vote
1answer
156 views

Is there a way to speed up JOIN ON LIKE?

I have millions of things in a table. I'd like to assign groups to the things based on theirs prefixes. I tried to add all the indexes but the planner still does nested loop left join. A demo case ...
32
votes
1answer
3k views

How does SQL Server determine key column order in missing index requests?

How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?
1
vote
3answers
41 views

Correcting a database that has had indexes and statistics created on it willy-nilly

I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and ...
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
0
votes
2answers
52 views

Define MYSQL index with condition

I have a table with these schemas: CREATE TABLE `transactions` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `description` varchar(255) NOT NULL, `is_deleted` ...

1 2 3
4
5
14