The process of determining which indexes are useful and which are not.
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
7
votes
4answers
244 views
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind:
Simplicity; there are no ...
7
votes
1answer
572 views
Should I use many single field indexes, instead of specific multi column indexes?
This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection".
I'm working with an existing SQL Server (2008) application that has a number ...
7
votes
1answer
378 views
What factors go into an Indexed View's Clustered Index being selected?
Breifly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
7
votes
2answers
337 views
Filtering data ordered by rowversion
I have a SQL table of data with the following structure :
CREATE TABLE Data(
Id uniqueidentifier NOT NULL,
Date datetime NOT NULL,
Value decimal(20, 10) NULL,
RV timestamp NOT NULL,
...
6
votes
2answers
307 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
6
votes
2answers
263 views
Adding a Clustered index to a HEAP table on someone else's app
We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
5
votes
2answers
560 views
Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition?
We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) ...
5
votes
2answers
176 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
5
votes
1answer
106 views
Is there any reason to use index for very small tables (up to 1000 rows)?
During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, ...
5
votes
2answers
2k views
What is the difference between ALTER INDEX and DBCC DBREINDEX?
Is the only difference between
ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90)
and
DBCC DBREINDEX([dbname], 90)
just that the DBCC command will reindex all ...
5
votes
2answers
149 views
Cardinality rule for bitmap indexes
The Oracle documentation includes the following advice:
A bitmap index should be built on each of the foreign key columns of
the fact table or tables
In that reference, there is even a bitmap ...
5
votes
2answers
455 views
How can I improve the speed of a query on a 20 million+ row table?
I have a query that is used for getting internet traffic statistics of certain IP addresses.
There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
5
votes
3answers
905 views
SQL server indexing foreign keys, covering indexes included columns
I have pulled a list of FK's that do not have indexes on them.
If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated ...
5
votes
1answer
396 views
SQL Server 2008 Maintenance Plan Failure
Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.
The details are:
Connection = Local server connection
...
5
votes
1answer
214 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
4
votes
4answers
651 views
How will large index INCLUDE fields effect system performance?
This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index.
I’m trying to improve performance in an slow and unstable database application. In some ...
4
votes
3answers
343 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
4
votes
2answers
174 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
4
votes
3answers
256 views
Using DTA vs. evaluating DMVs?
Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.
Form How to determine if an Index is required or necessary I have ...
4
votes
3answers
139 views
Most efficient (or meaningful) way to index grid-based data
I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
4
votes
2answers
2k views
Primary key vs. unique index: performance difference with foreign keys?
Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:
A primary key
A unique ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
1answer
65 views
Index Evaluation
I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index.
The non ...
4
votes
2answers
746 views
How I should fix the following update statement to make it run faster?
I have the following DML:
UPDATE rc_usutb011 u
SET cod_ant=(
SELECT cod_hst
FROM rc_pdptb101 p
WHERE ocip_hst=ocip_ant
AND p.sector=u.sector
);
The explain plan ...
3
votes
2answers
188 views
How do I know what indexes to create for a table
Is there a way I can figure out the best way to know which indexes to create for a table?
3
votes
2answers
104 views
index maintenance strategy if few insertion points relative to # of rows
In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
3
votes
1answer
50 views
What is a suitable index for this query?
Below query is taking long time - around 540 seconds.
The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.
...
3
votes
1answer
191 views
Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning
I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
3
votes
1answer
118 views
Filtered index statistics refresh threshold
We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date"
...
3
votes
1answer
106 views
What are the disadvantages if indexes are added on all columns?
I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?
Are ...
3
votes
1answer
76 views
How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?
I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key:
CREATE PROCEDURE ...
3
votes
1answer
187 views
Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?
My initial question was How does someone determine if it is a good idea to index a view?.
Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...
3
votes
2answers
245 views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
...
3
votes
1answer
699 views
Oracle not using index when > used in where clause
We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.
Because there is no end clause to the date range, oracle ...
3
votes
3answers
125 views
What is a good guideline for sampling for statistics updates
Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.
Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
3
votes
2answers
90 views
MySQL using a index referencing a different column
I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same ...
3
votes
0answers
46 views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
3
votes
2answers
246 views
Execution plan results for sp_executesql suggests adding missing index for already existing index
I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
2
votes
3answers
148 views
Determining if indexes are redundant
I have a table with the following structure:
Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields
The following indexes have been defined:
PK (Id), clustered
Index1 ...
2
votes
1answer
134 views
Oracle function based index is not used in query
I cannot figure out why the following is not working as I expect it :
CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
val1 VARCHAR(50));
CREATE INDEX ...
2
votes
1answer
99 views
Where to place non clustered index
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
2
votes
2answers
335 views
Reorganize Indexes T-SQL
I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
2
votes
1answer
129 views
Indexing or changing one-to-many relationship
I have these two Microsoft SQL Server 2005 tables:
ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004
Objects (ObjectID ...
2
votes
1answer
95 views
Are two indexes needed?
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.
Which indexes should I use? Should I have two ...
2
votes
1answer
203 views
Is staggering reindexing jobs a good strategy?
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.
...
2
votes
2answers
92 views
Optimizing index creation
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.
An example case would be:
table1
...
2
votes
1answer
65 views
Does updating a partially indexed field add or remove it from an index in Postgres
We can create a partial index for a field in Postgres.
I am looking at using this to stop rows which are archived from being in an index on a created_at field:
First we create the index:
test=# ...
2
votes
1answer
172 views
In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?
I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
2
votes
1answer
204 views
Unique insert performance: Primary Key vs Unique Index
I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records.
What's the fastest way to insert into the table, by keeping the domain unique ...
