Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [index]

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 sorted but structures the data differently to allow faster access.

An index can be added to a table to facilitate efficient lookups. If a query predicate is not supported by an index then it will be necessary to scan the entire table to identify rows matching the predicate.

Various data structures can be used for indexing, with particular characteristics. Some commonly used on-disk index structures are:

B-Trees and their variants allow O(log n) lookup times and efficient ordinal, sequential access to records (i.e. the tree can be traversed in order). BTrees are tree structures that hold multiple keys in their nodes, which is efficient for disk access. B-Tree type indexes (the actual structures tend to be variations on the BTree) are supported by most DBMS platforms.

Linear Hash Tables is a method of constructing a hash table that can grow incrementally without requiring the table to be rebuilt as it expands. This feature makes the structure useful for disk indexing. Linear Hash Tables cannot traverse data ordinally, but have O(1) lookup times for a single record.

R-Trees are used for indexing spatial data. Commonly used in purpose built spatial database systems, modern RDBMS platforms often feature spatial indexing capabilities as well.

Bitmap Indexes: Are used for applications such as reporting systems where queries combine searches on a number of low cardinality columns where any column has a small number of distinct values and thus low selectivity. The data structure used in bitmap indexes is very sparse, so it compresses very well with run-length encoding schemes. Index intersection computations can be resolved very quickly with bitmap indexes, so they are very efficient for certain types of quereies. However they cannot be updated incrementally, so they are mainly useful for applications such as data warehouse systems where the data is loaded in batch operations for a subsequent read-only workload.

Related: What does "index" means on RDBMS?

3163 questions
266
votes
7 answers

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id int primary key, code varchar(10) NULL ); go create…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
143
votes
4 answers

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this…
Stephane Rolland
  • 7,903
  • 8
  • 29
  • 40
136
votes
8 answers

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if there is a way to rewrite this to become more…
Lucas Kauffman
  • 1,695
  • 4
  • 16
  • 18
127
votes
7 answers

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that…
misterjaytee
  • 1,373
  • 3
  • 11
  • 8
119
votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? Additionally, I created an index on A, but Postgres still…
Luciano
  • 1,621
  • 3
  • 12
  • 8
104
votes
3 answers

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 (k, v) values (10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with no order…
daisy
  • 1,258
  • 3
  • 11
  • 14
91
votes
5 answers

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 user table Are these equivalent? If not then why?…
codecool
  • 1,923
  • 2
  • 15
  • 21
89
votes
4 answers

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an error in your SQL syntax;... MySQL version (mysql -V)…
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
81
votes
2 answers

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 to the column creation one that is solved with…
GuidoS
  • 967
  • 1
  • 6
  • 7
74
votes
2 answers

Understanding "bitmap heap scan" and "bitmap index scan"

I'll try to explain my misunderstandings by the following example. I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE customerid < 1000 AND username <'user100'; the…
St.Antario
  • 1,145
  • 1
  • 9
  • 14
74
votes
4 answers

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor this?
myahya
  • 841
  • 1
  • 6
  • 4
69
votes
4 answers

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index scan? Which performs better? How does SQL choose…
Greg
  • 3,202
  • 5
  • 29
  • 55
63
votes
3 answers

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization would be to cache the "Post" ids, but unfortunately…
Garrett
  • 933
  • 1
  • 10
  • 15
60
votes
4 answers

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the method below, which at least shows me movement…
Jonesome Reinstate Monica
  • 3,319
  • 9
  • 36
  • 55
58
votes
3 answers

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with 5-10 strings in the list): SELECT myField FROM…
Mark Tower
  • 683
  • 1
  • 5
  • 6
1
2 3
…
99 100