Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
48 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
54 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
255 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
58 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
0answers
35 views

Update stats as default or with a sample value

I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the ...
0
votes
1answer
76 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
29 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
148 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
244 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
416 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
48 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, ...
2
votes
0answers
115 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
29 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
105 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
325 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
104 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, ...
42
votes
2answers
37k 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 ...
9
votes
1answer
5k 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 ...
6
votes
1answer
3k views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
1
vote
1answer
86 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 ...
29
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
40 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
44 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` ...
15
votes
4answers
4k views

How will large index INCLUDE fields affect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
0
votes
1answer
49 views

MYSQL Multiple table optimization for advanced search

I have a users table where there is the basic information of each user: CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account_type` varchar(10) NOT NULL, `email` varchar(...
1
vote
0answers
72 views

Testing efficiency of a BRIN index in Postgres

I have found many use cases in our OLAP queries for BRIN indexes, as they are much much smaller and have comparable speed in the same order of magnitude as BTREE indexes. Typically I use BRIN indexes ...
0
votes
1answer
47 views

Autogenerate indices on postgresql or speed up queries

I have a problem that seems to come back from time to time on a client database. The current table that is causing most of the problems has around 200,000 records. I'd guess it's not technically a big ...
7
votes
6answers
7k views

Rebuild Very Large Primary Key Index

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all ...
3
votes
2answers
4k views

Table Valued Function Very Slow

I have a TVF that when called takes 10 secs to run. However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables. In the Execution ...
2
votes
1answer
176 views

Index Maintenance for DW- DB ( ETL process )

I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business ...
29
votes
2answers
27k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
2
votes
2answers
329 views

Why is this query with WHERE, ORDER BY and LIMIT so slow?

Given this table posts_lists: Table "public.posts_lists" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+-...
0
votes
1answer
55 views

PostgreSQL 9.5 query performance depends on JOINed column in SELECT clause

Data: Over the following two tables: Table "public.response_logs" Column | Type | Collation | Nullable | Default --------...
1
vote
1answer
116 views

How to index two tables for JOINed query optimisation

I'm using PostgreSQL 9.5 on High Sierra. Over the two tables: request_logs - ~ 26K rows response_logs - ~ 9K rows I've the following query (with JOIN): SELECT req.uuid, res.status, req....
5
votes
3answers
832 views

Why does this Index seek cause a lot more reads than index scan

As I've read that index seeks most of the time are preferred to index scans, I'm trying some stuff out. I have a query that does 993 reads (checked with SQL Profiler) when it's using an index scan. ...
-2
votes
1answer
59 views

Can rebuilding an index create fragmentation?

Its my understanding that when you rebuild an index SQL will sort the pages into order at the end of the data file, growing the data file until all pages are ordered and then drop the old index ...
0
votes
1answer
79 views

Query and index optimization for a table containing 10 million records [closed]

I was trying to retrieve data from a table A to retrieve data for 1 month, which has around 10 million records and 21 indexes built on it, It took me 3 min to retrieve around 20 columns and total of ...
4
votes
2answers
5k views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
1
vote
1answer
40 views

Index Management

I have two tables in MySQL 8, table A with primary key and table B with a foreign key of the same type on column 1. table B has an index on column 1. If I want to change the index on table/column B1, ...
0
votes
1answer
23 views

MongoDb use indexes partially

Can mongodb take advantage of an index partially? I'm not talking about using a "partial index" ( https://docs.mongodb.com/manual/core/index-partial/ ), but using a regular index partially. For ...
1
vote
1answer
490 views

Indexing strategy for VARCHAR2 LIKE search

Scenario Database Oracle Database 11g Table Users ID Number, PK CODE Number, Indexed, Unique, Not null FIRST_NAME VARCHAR(100), Not null LAST_NAME VARCHAR(100), Not null EMAIL VARCHAR(50), Not ...
3
votes
3answers
380 views

Does Index Rebuild Reduces physical read from disk

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index ...
1
vote
2answers
155 views

Database Space Used Decreased after Index Rebuild (Ola Script)

I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb) Is that normal behavior? I dont have ...
3
votes
1answer
134 views

Indexes: Normal tables vs memory optimized

If two tables have the same schema but one is "normal" and the other is memory optimized, if I research on how indexes should be for the "normal", can I do a direct extrapolation for the memory ...
0
votes
1answer
73 views

SQL Server - Non-reindexed Database [closed]

I have a database that has not been reindexed in the last 5 years. I want a yes or no answer if databases without index maintenance and "Missing Rows" are related to each other.
0
votes
1answer
39 views

Indexes for select performance

I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` enum('new','paid') NOT NULL DEFAULT 'new', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `...
2
votes
2answers
95 views

How to optimize a query for < operator

I have a SELECT that is very slow when using < operator I'm looking for fixes or workarounds to perform this operation: EXPLAIN (ANALYZE) SELECT * FROM "users" WHERE (engagement_level(social) &...