Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
906
questions
1
vote
2
answers
1k
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;
...
- 169
0
votes
1
answer
54
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 ...
- 169
0
votes
1
answer
1k
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, ...
- 227
1
vote
2
answers
946
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 ...
- 113
7
votes
2
answers
2k
views
Will my database index be more performant with a smaller varchar size?
We're using Mysql 5.7
We have an index over a varchar(255) column in a table of ~ 500M rows.
select distinct varchar_column from table yields seven results, with the max length 21.
A DBA consultant ...
- 173
1
vote
0
answers
76
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` ...
- 97
3
votes
2
answers
166
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 ...
- 163
1
vote
1
answer
32
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 ...
5
votes
3
answers
2k
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,...
- 53
1
vote
0
answers
138
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 ...
- 111
0
votes
2
answers
2k
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 ...
2
votes
2
answers
1k
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 ...
- 123
2
votes
1
answer
466
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 ...
- 149
1
vote
1
answer
1k
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 ...
- 123
1
vote
0
answers
99
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 ...
- 165
0
votes
1
answer
715
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, ...
- 678
1
vote
1
answer
103
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 ...
- 45
2
votes
3
answers
683
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, ...
- 123
3
votes
1
answer
39
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 ...
- 131
1
vote
1
answer
2k
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, ...
- 539
3
votes
1
answer
661
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 ...
- 199
5
votes
2
answers
465
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 ...
- 177
4
votes
1
answer
575
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 ...
- 131
1
vote
1
answer
91
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 (...
- 11
-1
votes
2
answers
193
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,
...
- 45
1
vote
2
answers
453
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 ...
- 135
0
votes
0
answers
265
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 ...
- 428
2
votes
3
answers
498
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 ...
- 41
8
votes
1
answer
437
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 ...
- 540
0
votes
1
answer
135
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, ...
- 41
0
votes
1
answer
1k
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 ...
- 143
2
votes
2
answers
381
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 ...
- 7,386
0
votes
1
answer
1k
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
...
- 247
5
votes
1
answer
8k
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 ...
- 103
4
votes
1
answer
8k
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 ...
- 103
-1
votes
1
answer
109
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 ...
- 37
1
vote
1
answer
344
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 (...
- 259
1
vote
2
answers
1k
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-...
- 113
1
vote
1
answer
188
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 ...
- 11
1
vote
3
answers
626
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 ...
- 111
0
votes
1
answer
703
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 ...
- 115
0
votes
0
answers
68
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 ...
- 2,169
2
votes
1
answer
383
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 ...
- 121
0
votes
1
answer
399
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
1
answer
57
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 ...
- 115
-1
votes
1
answer
349
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, ...
- 185
5
votes
1
answer
3k
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 ...
- 185
1
vote
1
answer
371
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 ...
- 11
0
votes
1
answer
3k
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 ...
- 103
0
votes
1
answer
480
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 ...
- 77