The process of determining which indexes are useful and which are not.
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=# ...
5
votes
2answers
562 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
277 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
346 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
90 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
173 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
118 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
465 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 ...
0
votes
0answers
141 views
Why this query takes 500 seconds to complete even though I got all the compound index right?
I tried this query
db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { ...
2
votes
1answer
136 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 ...
3
votes
1answer
192 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
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
337 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
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 ...
5
votes
3answers
910 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
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 ...
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 ...
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 ...
0
votes
1answer
70 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
1k 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
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"
...
1
vote
1answer
447 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
95 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 ...
3
votes
2answers
246 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
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
4answers
654 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 ...
7
votes
1answer
576 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
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 ...
3
votes
1answer
709 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
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 ...
7
votes
4answers
245 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 ...
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 ...
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 ...
