Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
1
vote
1answer
40 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 ...
1
vote
1answer
10 views
Identify proper index for group and order by date and hour (2 columns)
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, no parameters are ...
3
votes
0answers
17 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
0answers
35 views
Best index strategy to use? [on hold]
What kind of index strategy would you use if there is something with a lot of writes and is quite expensive?
I know there is no fixed index strategy but just wanted to know which one is the most ...
1
vote
1answer
29 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, ...
3
votes
1answer
101 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
110 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 ...
2
votes
1answer
335 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 ...
1
vote
1answer
37 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
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,
...
1
vote
2answers
48 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 ...
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 ...
2
votes
3answers
438 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 ...
7
votes
1answer
384 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 ...
0
votes
1answer
54 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, ...
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 ...
2
votes
2answers
90 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 ...
0
votes
1answer
28 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
...
1
vote
0answers
77 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 ...
2
votes
1answer
68 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 ...
-1
votes
1answer
53 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 ...
1
vote
1answer
55 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 (...
1
vote
2answers
53 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-...
1
vote
1answer
70 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 ...
1
vote
3answers
52 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 ...
0
votes
1answer
33 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
26 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
votes
1answer
49 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 ...
0
votes
1answer
59 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
27 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 ...
-1
votes
1answer
42 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
63 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 ...
1
vote
1answer
61 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 ...
0
votes
1answer
27 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 ...
0
votes
1answer
100 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
0answers
90 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
79 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, ...
1
vote
1answer
63 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 ...
10
votes
2answers
528 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
vote
3answers
37 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
27 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
46 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 ...
3
votes
3answers
79 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
38 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
vote
0answers
52 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
36 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 ...
0
votes
1answer
43 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
1answer
123 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 ...
0
votes
1answer
46 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
97 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....
