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

learn more… | top users | synonyms

3
votes
2answers
91 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
4
votes
1answer
160 views

Table with clustered Index consuming max memory causing low PLE alerts

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day: Server: SQL server 2008R2 Databases : ...
2
votes
2answers
192 views

Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index

Lets say for example that I have a table structured this way: ID1 int ID2 int ID3 int DTM datetime I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a ...
0
votes
1answer
87 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
1
vote
1answer
37 views

Clear missing index DMVs

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details? I don't have an option to restart the server or taking it ...
1
vote
1answer
33 views

How to determine efficacy of index on child side of foreign key

Given a parent-child relationship between two tables, such as: table ORDER order_id PK (already indexed) order_name table ITEM item_id PK (already indexed) order_id (FK to ...
0
votes
0answers
40 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
1
vote
0answers
47 views

Why is Update Query Slow with correct Indexes?

I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query. However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is ...
1
vote
2answers
37 views

Optimal indexing for SELECT (mysql)

What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows. SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at ...
1
vote
2answers
401 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
3
votes
2answers
269 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
0
votes
1answer
59 views

Disable TTL thread in MongoDB

Is there any way to disable TTL deletion thread in mongoDB? I know that you can modify the ttl index expireAfterSeconds value to a high number in order to "technically" stop ttl from deleting, but i ...
1
vote
1answer
93 views

Rebuild index in table with many values

I'm junior DBA ,have table with 11 million values ,one of the indexes is like : CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] ( [my_id] DESC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
521 views

SQL Server 2008R2 XML Column Index strategy for large tables?

I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
1
vote
1answer
112 views

Create nonclustered index on uniqueidentifier type

CREATE TABLE [dbo].[session] ( [id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [session_id] [uniqueidentifier] NOT NULL, [supervisor_id] [int] NULL, some columns .. ...
1
vote
2answers
2k views

Adding index to large mysql tables

I have a table | base_schedule_line_items | CREATE TABLE base_schedule_line_items ( id int(10) unsigned NOT NULL AUTO_INCREMENT, installment int(10) unsigned NOT NULL, on_date date ...
3
votes
2answers
48 views

Do Postgres GIN indexes need to be rebuilt periodically?

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
2
votes
1answer
633 views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
1
vote
1answer
51 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
1
vote
2answers
638 views

Find out when your index was last rebuilt / reorganized

I've recently come across a database, which has very high fragmentation on large tables. Page count > 1'000'000 with fragmentation 99,... I would like to know how long this table has gone without a ...
1
vote
1answer
78 views

EXECUTE within function not using index?

I have an accounts table with ~200k rows and an index for these columns: account_type_id BIGINT , member_id BIGINT , external_id VARCHAR(64) CREATE INDEX account_full_qualifiers_idx ON ...
3
votes
1answer
110 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
0
votes
0answers
118 views

Performance tuning self-joining MySQL query

I asked a question recently on the correct way to update a table using multiple joins to itself in order to generate the desired values. Updating MySQL innodb table based on multiple joins After ...
0
votes
1answer
47 views

MySQL uses intersection instead compound index

I have compound index on following columns: (country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count) When I run following query: SELECT COUNT(*) FROM `user_profile` WHERE ...
0
votes
3answers
121 views

Active/current (1-5%) and historical records in same table: index or partitioning?

I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, ...
1
vote
0answers
49 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
2
votes
1answer
190 views

How to index a query `WHERE field IS NULL` (postgres)

So I basically have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, ...
2
votes
1answer
87 views

Why am I getting cardinality issues when querying a single table

I have the following query: SELECT [iri].[RateItemKey], [iri].[RateItemType] FROM [prism72].[dbo].[ispRateItem] AS [iri] WHERE [iri].[Status] = 1 AND ( ...
2
votes
0answers
64 views

work/cache table issues, heavy inserts with heavy reads

we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day. during the updates, we delete ...
0
votes
1answer
28 views

Join Operation using index defined?

I need to optimize some queries with several joins. I plan to create some indexes for these queries. I am currently using MariaDB with TokuDB storage engine. Should I expect the indexes for the join ...
1
vote
2answers
198 views

Should these two indexes suggested by the SSMS missing index feature be combined?

I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework. I have run the query through SSMS, and it has suggested creating two missing indexes. ...
4
votes
1answer
345 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
1
vote
1answer
100 views

Reading a SQL Server Execution plan

I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my ...
2
votes
3answers
92 views

Change table scan in query plan to seek

I have an ORM generated query (Django) running against a local Postgres 9.1.11 instance that looks up ~5 records from two specific match fields. Due to the nature of the EDI load program, I need to ...
1
vote
0answers
57 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
18
votes
2answers
8k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
2
votes
1answer
104 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my ...
1
vote
1answer
545 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
2
votes
0answers
68 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
1
vote
1answer
686 views

Execution plan index suggestion - difference between similar queries

I am trying to analyze queries on this table: CREATE TABLE [dbo].[Values]( [tid] [smallint] NOT NULL, [t] [datetime2](3) NOT NULL, [v] [real] NOT NULL, CONSTRAINT [PK_Values_Unique] ...
3
votes
4answers
207 views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive ...
23
votes
2answers
6k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
0
votes
0answers
45 views

Speed up sorting in this query

I'm trying to optimize the following plan and noticed that PostgreSQL spends a lot of time (relatively speaking) on sorting before the merge joins. Is there a way (using indexes, cluster the table, ...
0
votes
1answer
484 views

SQL Server improving queries waiting on PAGEIOLATCH_SH / Memory/CPU

As an 'accidental DBA' of my company, one of my tasks is to identify possible performance problems of our ERP system. For complex reasons, the database back-end has to be SQL Server 2000. From ...
-1
votes
2answers
53 views

Indexing into two “buckets”

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater ...
1
vote
1answer
31 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
2
votes
1answer
244 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
3
votes
1answer
79 views

Does sp_indexoption cause index rebuilds?

I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index. It is not clear from the MSDN article ...
0
votes
1answer
32 views

select where index + other field

Learning indexing I have made an index, consisting of usergroupid and birthdate I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate But what ...
0
votes
2answers
91 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...