Questions tagged [index-tuning]

The process of determining which indexes are useful and which are not.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
15