Questions tagged [index-tuning]
The process of determining which indexes are useful and which are not.
744
questions
2
votes
2answers
66 views
Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR
We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
0
votes
1answer
38 views
INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?
When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes?
I'm specifically thinking of SQL Server here but I assume the ...
1
vote
1answer
20 views
Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?
I could obviously test this empirically but I've found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven't ...
9
votes
2answers
205 views
Why is SQL doing an index scan and seek with the same XML query structure?
I've set up a test to benchmark xml performance in SQL server.
Test Setup
One Million rows of data
XML defined Column with primary key
A primary XML index
A secondary XML index on the path
XML data ...
0
votes
3answers
48 views
Index Maintenance on busy OLTP database
I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition.
There is no budget for enterprise nor ...
0
votes
1answer
17 views
Query ignores index resulting full table scan
I have two tables from django in mysql 5.7, auth_user and account_account:
CREATE TABLE `auth_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(150) NOT NULL,
`first_name` varchar(...
-1
votes
1answer
43 views
When was my indexes in the database fragmented?
I am trying to create a script that finds the index fragmentation, have it log to a table that I can query each time it runs. I need to fins out specifically what time my indexes in a table get ...
0
votes
1answer
59 views
Use index in ORDER BY query SQLite
I have a table that store messages for my Chat app:
CREATE TABLE message (
currentUserId,
threadId,
timestamp,
message,
msgType,
msgState )
Then I used this query to load 20 ...
0
votes
1answer
20 views
Indexes and windowing functions
We have a design in which a table stores the history of changes to an entity, and a view queries the table for the latest row for that entity.
Say, employee_history and employee (the view with latest ...
7
votes
6answers
558 views
SQL Server Index Drops - Considerations
I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB. I am interested in the opinions of ...
0
votes
1answer
32 views
Indexing very similar character varying field in postgres [closed]
I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (...
0
votes
3answers
55 views
Difference between the following postgresql indexes
On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row.
The following 2 indexes, I would ...
0
votes
2answers
40 views
Optimize performance for query with lots of OR's
I have the following query
select Id
from Table1
where column1 = @inputparameter1
or column2 = @inputparameter1
or column3 = @inputparameter1
or column4 = @inputparameter1
or column5 = @...
-1
votes
1answer
48 views
How to tune ilike query involving multiple columns
SELECT * FROM patients WHERE "account_id" = 765 AND first_name ilike 'rahul%';
The query is currently taking 100ms plus, I have individually indexed account_id and first_name. First name is ...
1
vote
0answers
14 views
Query optimalization - how create proper composite index
I have mysql 5.5 and table item have around 1-3 milions rows.
CREATE TABLE `item` (
`Id_item` int(11) NOT NULL DEFAULT '0',
`item_Kod` varchar(15) DEFAULT NULL,
`Date1` datetime DEFAULT NULL,
`...
1
vote
0answers
30 views
Postgres index on timestamp column not utilized
I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column.
I have a query as below
SELECT
*
FROM
employee
WHERE
timestamp >=...
2
votes
1answer
107 views
Save performance with large update on Index with Included Column
Points
------------------
PK QuestionId int (+30.000.000 distinct values)
PK EventId int (large batches where 80.000 rows have the same EventId)
Value smallint
The table is about 40 ...
0
votes
1answer
50 views
Index automation job script
Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a ...
1
vote
2answers
27 views
How do I add composite MySQL Indexes with denormalization and JOINS?
I have the following tables:
CREATE TABLE base_event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_by ... -- some columns
);
CREATE TABLE transaction_events (
event_id ...
1
vote
2answers
86 views
Index with few seeks and many updates [closed]
Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the ...
1
vote
1answer
84 views
How to prevent RID lookup?
I have a table that stores time-series data and has about 8 million rows. The table structure is as follows:
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
I have a clustered index ...
1
vote
2answers
57 views
How to reduce fragmentation of nonclustered index on date partitioned log table
Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a ...
1
vote
1answer
46 views
Questions on updating statistics and index maintenance jobs
Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions:
For ...
7
votes
2answers
206 views
Clustered Index fragmentation vs Index with Included columns fragmentation
I have a relative large table (for me) with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign).
Tip
--------------
Id int (...
-1
votes
2answers
49 views
Rebuild Indexes [duplicate]
We have a database named WSS_Us_App in our Production SQL Server 2014. In this database all of the indexes need to be rebuilt as most have a fragmentation level greater than 50%. I plan to rebuild ...
0
votes
1answer
26 views
If i index two columns (indepentently) do i get the same performance as putting and index on the two
I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used.
From my understanding, if you had a BTREE index on each ...
-1
votes
3answers
60 views
Optimal Indexing Strategy
For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the ...
2
votes
2answers
123 views
MySQL multiple index columns have a full cardinality?
I have noticed indexes like:
+-------------------+------------+-----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+------...
0
votes
2answers
40 views
Question about composite indexes and WHERE + GROUP BY
Alrighty so I understand how composite indexes work with WHERE but what if a column in the middle of the index is in the GROUP BY, as follows:
CREATE TABLE `test` (
`a` VARCHAR(255) NOT NULL,
...
0
votes
1answer
52 views
Do triggers cause extra index updates?
I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case.
Long story short they are never setting nullable fields to null. They are putting in ...
0
votes
0answers
44 views
Postgresql multicolumn index on tsrange and text columns
I have a complex query on a very small device (ARM9 @ 400 MHz with 256MB of RAM)
the postgresql db table I am querying has up to 10M rows.
SELECT *
FROM records_list a
INNER JOIN records_list b
...
0
votes
1answer
57 views
Indexing to support advanced search queries
I have a Person table 2.5 million rows, typical demographic fields. Need to support an advanced search screen that allows up to 10 search params (f/l name, phone 1 through phone 6, email, nickname, ...
5
votes
1answer
92 views
Is there any reason to have nonclustered indexes on a table with only 1 page in the clustered index?
I have a small but actively queried table in SQL Server that has 94 rows that are frequently read and\or updated. The clustered index safely fits into 1 8KB page and has significant empty space on ...
0
votes
2answers
92 views
Clustered index in ETL (drop - insert - create?)
I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is ...
1
vote
2answers
48 views
Index Maintenance on Clustered PK Index
I know there are similar question to this one, but I couldn't find one dealing with Index Maintenance...
I have some PK Clustered Indexes [int (auto increment).] I always notice that these Indexes ...
0
votes
2answers
56 views
How can i tune below proc either by changing query or adding index
I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on ...
1
vote
2answers
112 views
MariaDB (MySQL) slow query when primary key range combined with fulltext index
I've a table described below, with two columns - integer primary key and title text - currently holding circa 3 million records. As seen in the metadata below, there's a BTREE index on integer primary ...
3
votes
1answer
113 views
Indexing Referenced Table Columns
I would like to do a search across columns of joined tables. What would the best index configuration be for the query below?
Table A
------------
Id
ValueA
-- Table A has many more columns
Table B
---...
0
votes
1answer
28 views
@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set
We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day. Therefore, we set the @timelimit variable / parameter to 23400 seconds. The issue is that the ...
0
votes
1answer
45 views
Calendar Event table - best practice setup for range queries and individual retrieval
This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is ...
2
votes
1answer
79 views
Searching INET[] for CIDR match, efficiently using indexes (without UNNEST)
This question stems from having recently discovered that the following are very, very different in terms of performance
DROP TABLE IF EXISTS dns_lookup;
CREATE TABLE IF NOT EXISTS dns_lookup (
...
1
vote
2answers
84 views
Why is PostgreSQL Not Using My Index?
I have the following query:
SELECT "account_transactions"."subsidiary_id"
FROM "account_transactions"
WHERE (transaction_type = 'loan_payment'
AND amount > 0
...
0
votes
1answer
105 views
Slow query, indexes are in place. Help!
Here is the query:
EXPLAIN
SELECT
f.transaction_id, a.first_name
FROM
gateway_fees_stripe f
INNER JOIN payments_credit_card a
ON a.`transaction_id` = f.transaction_id
WHERE f.organization_id IN (262,...
2
votes
2answers
147 views
Speeding up index scan backwards query
My app is making the following psql query, and it is running extremely slow:
SELECT COUNT(*)
FROM (
SELECT 1 AS one
FROM "large_table"
WHERE "large_table"."user_id&...
1
vote
1answer
39 views
What is most efficient way to store one or two chars?
In Postgres table with ca million rows I have a column bookingtype (character varying(20)), which default value is null and besides that it may have 7 distinct values (1-2 chars). Current situation:
...
1
vote
1answer
96 views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
1
vote
0answers
17 views
Analyzing performance of an insert int postgres
I have a statement which is inserting a bunch of rows (doesn't really matter what or where for this question) into a Postgres DB and it isn't as fast as I'd like. I can run an explain query to see ...
0
votes
1answer
17 views
MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly
I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table.
JobTable (...
0
votes
1answer
31 views
Why does my Query use a Bitmap Heap Scan
my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan.
explain analyze verbose
SELECT connections, epoch_time
FROM connections
WHERE host_name = 'xyz.z'
ORDER ...
1
vote
1answer
61 views
Clustered and columnstore indexes
I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So ...
