Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
3
votes
1answer
117 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 ...
0
votes
1answer
21 views

Sentry One Plan Explorer History Tab

While I'm working on index tuning I would love the history bar to populate with each run so I can see the differences, I'm probably using the tool incorrectly. My current workflow is: Create New Plan ...
0
votes
1answer
59 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 ...
0
votes
1answer
49 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 "...
2
votes
1answer
401 views

Oracle using sub-optimal index on bind variables

Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third ...
1
vote
2answers
154 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
0answers
60 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 ...
12
votes
6answers
8k 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 ...
0
votes
3answers
55 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
-1
votes
3answers
56 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 ...
4
votes
3answers
1k views

Extremely slow query on indexed column in Postgres

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ----...
1
vote
1answer
45 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 ...
0
votes
3answers
918 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 ...
1
vote
4answers
1k views

Find and Remove Duplicate Indexes

I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on ...
1
vote
1answer
342 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 ...
2
votes
1answer
61 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
57 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 ...
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
35 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
122 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? ...
1
vote
1answer
143 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 ...
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 ...
4
votes
2answers
2k views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...
0
votes
1answer
97 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
164 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 ...
0
votes
1answer
162 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 ...
0
votes
1answer
58 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, [...
3
votes
1answer
311 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 (...
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 ...
5
votes
1answer
274 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
0
votes
1answer
32 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='...
14
votes
0answers
793 views

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
-1
votes
1answer
415 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,...
18
votes
2answers
2k views

Why filtered index on IS NULL value is not used?

Assume we have a table definition like this: CREATE TABLE MyTab ( ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY ,GroupByColumn NVARCHAR(10) NOT NULL ,WhereColumn DATETIME NULL ...
-1
votes
1answer
222 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 ...
-1
votes
1answer
686 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 : +-----------------------+--------+---------+------------...
-1
votes
2answers
573 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 ...
2
votes
1answer
629 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
1
vote
1answer
207 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 ...
1
vote
1answer
72 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
211 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
73 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
287 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 ...
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
45 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 ...