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

learn more… | top users | synonyms

1
vote
1answer
200 views

Simplify and optimize a complex query

Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
5
votes
2answers
490 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 ...
1
vote
2answers
883 views

Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
2
votes
3answers
159 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
280 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. ...
5
votes
1answer
465 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 ...
3
votes
2answers
127 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
289 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, ...
0
votes
1answer
61 views

Is it any potential problem with having 2 constraints suppored by one index in Oracle?

I have the following table : CREATE TABLE action (action_id INT NOT NULL, action_type_id INT NOT NULL, action_date DATE NOT NULL); CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, ...
4
votes
2answers
2k 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
3answers
155 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 ...
7
votes
2answers
516 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, ...
7
votes
1answer
770 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 ...
2
votes
1answer
101 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=# ...
5
votes
2answers
1k 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) ...
1
vote
0answers
403 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
4
votes
3answers
690 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 - ...
1
vote
1answer
92 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
1
vote
1answer
264 views

Analyzing TCB Index Status for DB2

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean? TCB Index Stats: Address ...
1
vote
1answer
161 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
5
votes
2answers
2k 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 ...
2
votes
1answer
183 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 ...
4
votes
1answer
300 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 ...
2
votes
1answer
115 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
423 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 ...
4
votes
2answers
3k 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 ...
5
votes
3answers
1k 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 ...
2
votes
1answer
164 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 ...
3
votes
2answers
99 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 ...
2
votes
1answer
101 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 ...
0
votes
1answer
77 views

Can you search using an index while indexes are being updated in oracle11g?

I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations. Can I still search based on an index while that index is being updated in the database?
0
votes
3answers
2k views

Will more indexes on a table affect performance? [duplicate]

Possible Duplicate: How do indices impact query performance? How to know when/if I have too many indexes? Say ‘X’ report using some "where" condition in the statement so we are creating ...
3
votes
1answer
164 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" ...
1
vote
1answer
774 views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
0
votes
1answer
128 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
4
votes
2answers
371 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; ...
4
votes
3answers
195 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 ...
6
votes
4answers
1k 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 ...
9
votes
1answer
2k 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 ...
5
votes
2answers
3k 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 ...
3
votes
1answer
1k 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 ...
2
votes
1answer
242 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 ...
7
votes
4answers
302 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 ...
0
votes
1answer
1k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
10
votes
2answers
3k 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 ...