Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [index-tuning]

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

2
votes
3answers
118 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,...
2
votes
1answer
83 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
1
vote
1answer
17 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
19 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
614 views

How to make MySQL use indexes in an integer range select query

I'm doing a select on a table looking for rows that match between an upper and lower bound SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ; the ( simplified ) create ...
0
votes
2answers
29 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
66 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
98 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
1
vote
1answer
117 views

What index can I create to help a query using multiple datetime columns and datetime functions in its where clause?

SQL Server 2016 I have the following query: SELECT [Id], [Descriptor] FROM [Networks] WHERE ( [ValidFrom] <= SYSDATETIMEOFFSET() AND SYSDATETIMEOFFSET() < COALESCE ...
1
vote
1answer
85 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 ...
80
votes
3answers
27k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
6
votes
2answers
2k 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
40 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
1answer
93 views

SQL Server Database Tuning Advisor - Temp index being dropped

Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes. While ...
1
vote
0answers
33 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
92 views

What index(es) and queries for multiple column ranges search?

Based on a simple innodb mysql 5.6 database schema like this: What is the optimal indexing strategy for querying the "info" table where most of the search criteria will be IN and BETWEEN conditions? ...
0
votes
1answer
92 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
0
votes
1answer
47 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
63 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
0answers
22 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 ...
3
votes
1answer
127 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
103 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
1
vote
1answer
41 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, ...
2
votes
1answer
339 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
113 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
120 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
140 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 ...
0
votes
1answer
257 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 cores,...
0
votes
1answer
134 views

Tuning read-intensive queries - Oracle

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads ...
2
votes
2answers
571 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
40 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
54 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 ...
5
votes
1answer
318 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
386 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 ...
-1
votes
2answers
162 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, ...
3
votes
1answer
196 views

PostgreSQL is not using index with large data

Enviroment My PostgreSQL (9.2) schema looks like this: CREATE TABLE first ( id_first bigint NOT NULL, first_date timestamp without time zone NOT NULL, CONSTRAINT first_pkey PRIMARY KEY (...
0
votes
1answer
157 views

postgresql reindex frequency and monitoring

What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
0
votes
1answer
286 views

How can Mysql server auto re-index tables

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table. Ex : +-----------------------+--------+---------+------------...
0
votes
2answers
202 views

MySQL query optimization not using index

I'm running into an issue with my MySQL database and I would like to know if any of you could please shed some light. Background: I currently have entire schema configured with InnoDB. This database ...
1
vote
1answer
165 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
1answer
189 views

Indexes involved in intervals collision detection

Let's assume an hotel booking application wants to display all customers being present between two dates. Starting with a venue table : CREATE TABLE `venue` ( `id` INT NOT NULL AUTO_INCREMENT ...
0
votes
0answers
43 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
182 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
0
votes
1answer
55 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
440 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
257 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 ...
0
votes
1answer
17 views

Performance recommendations stuck on 'Validating'

I have been tasked with migrating a 'database from hell' to Azure SQL. It has some complicated and long-running queries (20 seconds on the S6 tier, >600 seconds on S4 tier) and I'd like to optimise ...
0
votes
1answer
223 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
-1
votes
1answer
59 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 ...