Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
0
votes
1answer
23 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
...
0
votes
0answers
31 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
56 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
vote
0answers
64 views
Query performance of searching pattern
Posted the same question on SO, but did not get answer.
As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front ...
-1
votes
1answer
43 views
Composite index and Normal index,redundant/duplicate index - Incurred Performance penalty
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
51 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
45 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
66 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 ...
0
votes
0answers
25 views
How to get a good balance between the performance of various queries and the number of indexes
With the following table, I'm worried either one of my queries will perform poorly,
or I'll have so many indexes that all queries perform well but writes are too slow.
Here's my table:
...
1
vote
3answers
46 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
30 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
22 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
47 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
54 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
26 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
41 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
51 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
53 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
25 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
79 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
45 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
74 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
55 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 ...
9
votes
2answers
386 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
35 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
44 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
76 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
33 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
40 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
40 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
113 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
41 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
83 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....
2
votes
2answers
196 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
------------+------------------------+-----------+-...
-2
votes
1answer
55 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
71 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 ...
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
19 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 ...
3
votes
3answers
350 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
1answer
143 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 ...
1
vote
2answers
134 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
94 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
37 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
93 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) &...
1
vote
1answer
60 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 ...
23
votes
4answers
5k views
If a database only ever has one insert, is it bad to index every possible column combination?
I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is ...
0
votes
3answers
109 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 ...
