A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.
5
votes
1answer
107 views
SQL Server: Effect of Setting These Index Options
A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database.
This was done while testing new reindexing ...
5
votes
2answers
396 views
Dropping and recreating indexes for performance reasons?
I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
5
votes
1answer
322 views
Will a clustered index improve performance of updates performed sequentially on that clustered index?
I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in ...
5
votes
2answers
187 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
5
votes
1answer
71 views
RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise
Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?
The last two weeks one of our applications has had downtime due to ...
5
votes
3answers
425 views
Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance
We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like
PK BIGINT
ID VARCHAR(50)
Col1
Col2
etc
So we need to insert a row in the ...
5
votes
2answers
237 views
Optimizing IP Range Search?
I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.
In my table I have 3 columns
CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
5
votes
1answer
756 views
Oracle concatenation of queries and function index usage
I have a problem with something I can't understand about indexes in Oracle 11g.
We can create test data with:
create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
5
votes
1answer
715 views
MySQL INNODB corruption after server crash during concurrent truncate command
My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
5
votes
3answers
167 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
5
votes
1answer
181 views
Query for data that is not there
I have this setup
attendance table:
------------------------------------------
| member_id | attendance_date | event_id |
------------------------------------------
attendance indeces
...
5
votes
1answer
440 views
Can filtered indexes help improve queries that are based on a time entered or should this be avoided?
I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help.
I can see that adding a filtered index to an employees ...
5
votes
1answer
73 views
When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?
Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)
I have a table that's got 19 ...
5
votes
2answers
270 views
Recreate Indexes on 1 billion record table
I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
5
votes
1answer
140 views
Walking a BTREE index as far as possible in MySQL
Suppose one has a column of words on which one builds a BTREE index:
CREATE TABLE myTable (
words VARCHAR(25),
INDEX USING BTREE (words)
);
LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
5
votes
1answer
56 views
SQL Server 2008 Index
What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K.
select
...
5
votes
1answer
257 views
Tuning queries and indexes using index analysis tool
I've been pointed to this script from several places which apparently helps with designing indexes in Microsoft SQL Server: http://indexanalysis.codeplex.com/
The problem is I'm using Sybase ASA 11, ...
5
votes
2answers
250 views
Partition Function vs. Clustered Index
We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
4
votes
5answers
251 views
How much does an index need to narrow the results of a search in order to be useful?
How much does an index need to narrow the results of a search in order to be useful in speeding up queries?
Some examples all across the spectrum:
A column for storing true/false values obviously ...
4
votes
4answers
429 views
How are foreign key indexes helpful?
How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
4
votes
2answers
523 views
Postgresql 9 speeding up indexed inserts (JPA)
I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
4
votes
4answers
1k views
When to add indexes to #temp tables?
When testing stored procedures in SSMS, it sometimes says there is a missing index on
#someTempTable____________________000000000000005B] (someField) etc etc
When I do add them like this to the sp:
...
4
votes
2answers
128 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
5answers
1k views
Improve sort performance in PostgreSQL?
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
4
votes
2answers
89 views
Performance of SQL Server indexing
I want to see the performance differences between an indexed vs non-indexed column when running the same query.
The table I've created (FileCollection) is very simple and has the following 3 columns
...
4
votes
3answers
66 views
Interpreting an execution plan
I need some help analysing some simple t-sql execution plans.
I have a table [User] with the following 3 columns:
Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))
I have Created an ...
4
votes
3answers
770 views
Detect Index Coalesce
Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.
Note: I am not referring to the COALESCE ...
4
votes
2answers
197 views
Computed column with less size than the “referring column”
I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
4
votes
2answers
2k views
SQL Server 2005 Index Rebuild/Reorganize
I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing ...
4
votes
1answer
1k views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
4
votes
1answer
90 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
4
votes
2answers
205 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
2answers
444 views
Index Generation in Postgres
We have a database with a single table made up of around 700 million entries.
We update the database by adding new entries on a server then transfer the server to the production server using pg_dump:
...
4
votes
2answers
207 views
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
4
votes
1answer
291 views
Index defrag takes forever to run
We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last ...
4
votes
2answers
442 views
Performance degradation after rebuilding indexes
I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
4
votes
2answers
244 views
Clustered indexing is now must - why?
Earlier, there were non-conclusive to me debates/discussions on whether to (always) engage/avoid clustered indexes.
Well, I understood that they are to be used sometimes with proper + specific ...
4
votes
2answers
1k views
How to promote an existing index to primary key in PostgreSQL
I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index ...
4
votes
1answer
530 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
4
votes
2answers
90 views
We are running database maintenace with indexing the system, indexing fails due to locking?
We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
4
votes
1answer
232 views
Creating an index in both asc and desc directions
Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table ...
4
votes
3answers
440 views
Do I need to add a new single column index to a table if a multi-column index on that field already exists?
I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__.
Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that ...
4
votes
1answer
759 views
Indexing to improve performance of range queries
I've got a relation in a Postgres 8.2 database similar to this:
CREATE TABLE foo (
foo_id varchar(160) NOT NULL,
bar_id varchar(160) NOT NULL,
created bigint NOT NULL,
PRIMARY KEY ...
4
votes
1answer
285 views
How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?
I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating ...
4
votes
1answer
193 views
Does UPDATE INDEXES work for local indexes?
I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):
CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
PARTITION p1 VALUES(1),
PARTITION ...
4
votes
1answer
194 views
How are indexes represented in an ERD?
What do indexes look like in an Entity Relationship Diagram?
I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
4
votes
2answers
612 views
Should a table have a clustered index even if it doesn't have appropriate fields for it?
If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices ...
4
votes
2answers
585 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
4
votes
2answers
50 views
How to design indexes for columns with NULL values in MySQL?
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
