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.

learn more… | top users | synonyms (2)

5
votes
1answer
419 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, ...
1
vote
1answer
567 views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
2
votes
2answers
980 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
2
votes
2answers
491 views

Rebuilding Index - sort in temp db

What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)? Where else does sorting take place?
1
vote
1answer
508 views

How to prevent big performance impact during index creation

I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is ...
3
votes
2answers
570 views

Is there any index with O(1) complexity for lookup in PostgreSQL?

Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?
6
votes
1answer
1k views

Using FORCE INDEX

I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O. It was something to do with the MySQL Query Optimizer not needing to go ...
2
votes
2answers
200 views

In MySQL, should I add an index even if the query that scans the table is only ran once a month?

Concretely, is it worth it to add an index to a query that scans the table of a mysql database, even if that index would only be used once a month? For example, having a users a table and then ...
13
votes
5answers
492 views

Indexing from start or when performance problem arises?

My question is regarding use of indexes. Should I start indexing right from the start or when performance problem arises? We can also create temporary index while executing a query. What are the ...
2
votes
2answers
160 views

How to index this table (a_level, b_level, item_id)

I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only) Lets say we have million ...
8
votes
3answers
296 views

Included Index Vs Filtered Index

We currently work with table named tb_tranfers. This table has 40 millions rows and is ~26 GB in size (11 GB Data, 15 GB Indexes) 10 to 15% of the rows are soft deleted rows (DeletedDate Is Not ...
0
votes
1answer
345 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
3
votes
2answers
1k views

How does Oracle handle composite index lookups?

MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix: MySQL cannot use the index to perform lookups if the columns do ...
2
votes
3answers
2k views

What impact do indexes have on the performance of table modifications?

I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table? My gut ...
25
votes
5answers
7k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
9
votes
3answers
6k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t(k int, v int,index k(k)) engine=memory; insert into t values(10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with ...
7
votes
3answers
7k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
4
votes
1answer
206 views

Strictly only RAM matters?

I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text. And id would be set to BIGINT and PK then if 8+2 (extra 2 ...
3
votes
3answers
2k views

When do hash indexes become reasonable?

MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking. And it seems to be widely understood that if the text field is ...
2
votes
2answers
379 views

What does “GIN doesn't support full index scans” mean?

I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be ...
7
votes
2answers
263 views

Why is Oracle using the index here?

Name Null Type -------- -------- --------- ID NOT NULL NUMBER(4) GROUP_ID NUMBER(4) TEXT CLOB There is a btree index on group_id. Here's how many ...
1
vote
2answers
312 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
11
votes
2answers
5k 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 ...
2
votes
1answer
167 views

What are hypothetical indexes?

They seem to be created by tools like DTA. For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.
18
votes
3answers
11k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
1
vote
1answer
129 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
5
votes
3answers
1k views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
2
votes
1answer
2k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
2
votes
2answers
439 views

Full Name search query

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please. We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
0
votes
2answers
280 views

FBI on function with concatenated string

I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") When trying ...
5
votes
3answers
229 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
4
votes
1answer
1k views

SQL Server 2008 R2 reindex job reorganise getting stuck

I am currently experiencing this job getting stuck in my nightly maintenance solution - ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled] REORGANIZE WITH ...
1
vote
1answer
274 views

Optimising a sql correlated subquery statement

I am updating a column on tableA with data from tableB like this: update tableA set bKey = select bKey from tableB B where B.colB = tableA.colB bKey has a unique index on tableB. Which is the ...
4
votes
2answers
128 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 ...
3
votes
1answer
203 views

Minimum rowcount for filtered index?

Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known. But with the advent of ...
9
votes
2answers
4k views

How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
1
vote
3answers
64 views

SQL Server - Index Including PK

Lets assume we have a table as follows: PK -- nonclustered primary key Col1 -- unique clustered Col2 Col3 Is this index: nonclustered index on (Col2, Col3) any different from this index: ...
17
votes
4answers
12k views

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (e.g. SQL Server)? Is there a case for rebuilding indexes on a regular basis?
4
votes
2answers
685 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: ...
3
votes
2answers
123 views

What would be the best way to model my simple table?

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4? Or, searching for the right row through a table with only 1 column with ...
5
votes
1answer
59 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 ...
2
votes
2answers
394 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
17
votes
7answers
640 views

Where can I find some guidance on index strategies?

Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded. As a general rule, which fields should be indexed? Which fields should ...
3
votes
3answers
704 views

Does this computed CHECKSUM() index design make sense?

I have come across what looks to me like a slightly odd pattern in a SQL Server 2005 database I'm taking care of, and was wondering whether it's just me, or whether it really is odd. There are a ...
2
votes
1answer
115 views

Index issue on order by column while applying Union all

I have two tables with 6 columns in 1st table and 6 in the second one. I have to combine the result of these two tables in order to make them in one set. Below are the structures of my tables: CREATE ...
2
votes
1answer
138 views

InnoDB foreign key and index duplication?

I'm have a part table like this: part_id INT manufacturer_id INT supplier_id INT part_number VARCHAR(50) part_description VARCHAR(120) with foreign ...
4
votes
1answer
1k views

Storing hex values as strings or integers?

I've inherited a database that uses sets of 32-byte hex strings to attempt to uniquely identify objects. Each string is called a fingerprint and each object can have up to 20 fingerprints. I'm ...
4
votes
2answers
2k views

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
5
votes
1answer
1k views

Estimating impact of indexes SQLite database size

I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to ...
2
votes
2answers
301 views

Search on part of a column in a SQL Server cross-database view

I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...