The process of determining which indexes are useful and which are not.
2
votes
0answers
39 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
52 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
25 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
0answers
20 views
Should I add `date` to my multicolumn primary key (PSQL data warehouse)?
date isn't needed to uniquely identify rows, but it is used in almost every query. I could add a separate index on date but wondered if maybe I should just add it as the first term in the primary key ...
-1
votes
1answer
32 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
45 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 ...
0
votes
0answers
19 views
How should we select proper columns to create indexes? [duplicate]
For a given query, I need to know how we select the suitable columns to create indexes. In oracle documentation I found below considerations,
The column is queried frequently.
A referential integrity ...
1
vote
1answer
46 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
72 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
36 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
69 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
43 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 ...
7
votes
1answer
262 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
33 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
1answer
34 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
2answers
69 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
30 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
33 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
33 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
37 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
106 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
31 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
77 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
176 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
51 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
66 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
18 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
345 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
107 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
127 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
90 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
36 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
92 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
56 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
2answers
68 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 ...
1
vote
1answer
37 views
Are many two column indexes a bad smell?
I have table that contains failures. (15000 rows + on a abstract embedded system)
A failure has the following fields:
id PK BIGINT
errorNumber INT
source VARCHAR
raisedAt BIGINT
clearedAt BIGINT
...
-2
votes
1answer
36 views
Index not hitting while querying [closed]
I have a query on a table tab and this table has a column a:
select * from tab where a = :a;
but the index is not hitting. Please let me know what could be possible reason (It was asked during an ...
1
vote
1answer
89 views
SQL Queries Slow in the Upgraded environment
I setup and configured an new SQL database server with SQL Server 2016 Standard Edition.
This is going to replace our existing SQL database server which is 2008 R2 database server.
I restored recent ...
1
vote
1answer
88 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:
...
28
votes
1answer
2k views
How does SQL Server determine key column order in missing index requests?
How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?
-2
votes
2answers
56 views
Add Argument to IndexOptimize.sql to Execute Custom Command After Each Index Is Processed [closed]
I need some way to check the space usage and backup the transaction log of the database before it gets full when we run your IndexOptimize.sql script. Our databases are mirrored, so I cannot change ...
1
vote
1answer
86 views
Query using a filtered index but wrong estimated number of rows
I have a table with a nonclustered index on a datetime2 field.
On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(...
2
votes
2answers
79 views
Optimizing a simple query joining two big tables
I don't understand why the following query is so slow (with Postgresql 9.6):
SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour
FROM ...
0
votes
2answers
85 views
Worse performance after create empty table
I'm working with MySQL 5.7.10 and I have this issue.
I have this table to trace requests:
CREATE TABLE `invoice_requests` (
`REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',...
0
votes
1answer
16 views
Mysql - optimal indexing for query using IN operator
I have this query:
SELECT *
FROM table1
WHERE foo IN (1,2,3,4) AND bar = 123;
I have 3 indices:
ALTER TABLE table1 ADD INDEX IDX_foo (foo);
ALTER TABLE table1 ADD INDEX IDX_bar (bar);
ALTER TABLE ...
