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.
49
votes
7answers
6k views
Should I index a bit field in SQL Server?
I remember reading at one point that indexing a field with low cardinality (a low number of distinct values) is not really worth doing. I admit I don't know enough about how indexes work to ...
39
votes
6answers
8k views
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 ...
20
votes
4answers
2k views
What does “index” mean on RDBMSs? [closed]
I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation ...
17
votes
4answers
479 views
Is "CREATE INDEX` in MySQL a Linear Operation?
What I mean is the following:
If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time.
What I'm trying to achieve ...
17
votes
4answers
4k views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
16
votes
6answers
1k views
Why don't databases create their own indexes automatically?
I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
16
votes
7answers
394 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 ...
16
votes
3answers
5k views
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 ...
16
votes
3answers
2k views
Is it a good idea/approach to index a VARCHAR column?
We're using PostgreSQL v8.2.3.
There are tables involved: EMPLOYEE and EMAILLIST.
Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)
2 tables are ...
15
votes
2answers
576 views
Do I need separate indexes for each type of query, or will one multi-column index work?
I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic.
My basic understanding is that generally speaking, a single index that ...
14
votes
3answers
1k views
Painless way to create a clustered index on a huge table?
So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
14
votes
3answers
892 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
14
votes
2answers
370 views
Why did creating this new index improve performance so much when existing index included all columns in new index?
I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems
The query in question is complicated so I'm ...
13
votes
5answers
3k 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 ...
13
votes
2answers
675 views
Primary key index with a DATETIME as first part of the compound key is never used
I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY.
I use MySQL 5.5
Here are my two tables:
-- This is my standard table with dateDim as a dateTime
CREATE ...
12
votes
5answers
441 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 ...
12
votes
4answers
4k 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?
11
votes
2answers
830 views
Is there any tangible difference between a unique clustered index and a clustered primary key?
I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
11
votes
2answers
252 views
Are there any benefits in certain ordering of columns when defining indexes
For example, if I have two indexes:
CREATE INDEX IDX_1 ON MY_TABLE_1
(ITEM, DATE, LOCATION)
COMPUTE STATISTICS;
CREATE INDEX IDX_2 ON MY_TABLE_1
(DATE, LOCATION, ITEM)
COMPUTE STATISTICS;
...
11
votes
3answers
739 views
Is there any benefit to defragmenting SQL indexes in a SAN environment?
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.
We have been running Ola Hallengren's index maintenance scripts weekly, and ...
10
votes
2answers
1k views
Can I create index on a table in a MySQL database while in use
And if I can, any reason (performance/stability) why I shouldn't?
10
votes
1answer
880 views
How do databases store index key values (on-disk) for variable length fields?
Context
This question pertains to the low-level implementation details of indexes in both SQL and NoSQL database systems. Actual structure of the index (B+ tree, hash, SSTable, etc.) is irrelevant as ...
9
votes
4answers
264 views
Determining that Indexes on a table are unused
I’ve been running this script to try to find extraneous indexes
select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SpaceInMB, s.*
from ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
9
votes
4answers
565 views
Where should one put indexes in a time dimension table?
After reading the Questions and Answers from this website about indexes, a question came to my mind.
What if, one is using a time dimension table with the lower level of granularity being the day. ...
9
votes
1answer
306 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
9
votes
1answer
707 views
What's the anatomy of a columnstore index?
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.
I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
9
votes
1answer
3k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
1answer
807 views
When should you specify PAD_INDEX?
So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
8
votes
5answers
2k views
When should nonclustered indexes be stored on separate filegroups?
I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
8
votes
2answers
705 views
Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?
I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...
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 ...
8
votes
2answers
149 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
2answers
424 views
SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?
On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? ...
8
votes
2answers
698 views
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?
...
8
votes
4answers
964 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
2answers
598 views
7
votes
4answers
4k views
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 ...
7
votes
3answers
481 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
4answers
620 views
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 ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
7
votes
3answers
262 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 ...
7
votes
2answers
495 views
At What Point Does Having an Index Become Efficient
I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large. This creates a tradeoff, which is a design ...
7
votes
1answer
203 views
SQL Server Index vs Statistic
What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
7
votes
2answers
766 views
Is there a way to force an index to stay in memory with SQL Server 2008?
I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
7
votes
1answer
3k views
Does mysqldump export indices, by default?
I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:
--disable-keys, -K
which suggests, ...
7
votes
1answer
432 views
Why does the fill factor in SQL Server default to 0 (100%)?
I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?
Is ...
7
votes
2answers
229 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 ...
7
votes
2answers
233 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
