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.
3,364
questions
282
votes
7
answers
157k
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 ...
- 12.3k
148
votes
4
answers
168k
views
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 ...
- 8,781
147
votes
8
answers
241k
views
Pattern matching with LIKE, SIMILAR TO or regular expressions
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 ...
- 1,815
131
votes
7
answers
155k
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 ...
- 1,413
129
votes
3
answers
65k
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?
...
- 1,741
111
votes
3
answers
171k
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 (k, v)
values (10, 1),
(10, 2),
(10, 3);
When ...
- 1,328
96
votes
5
answers
43k
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 ...
- 2,013
94
votes
4
answers
183k
views
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 ...
- 11.8k
88
votes
2
answers
144k
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 ...
- 1,047
87
votes
4
answers
70k
views
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 ...
- 971
84
votes
2
answers
56k
views
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 ...
- 1,245
70
votes
3
answers
117k
views
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 ...
- 1,043
70
votes
4
answers
59k
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 ...
- 3,272
65
votes
4
answers
86k
views
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 ...
64
votes
3
answers
95k
views
How to install the additional module pg_trgm
I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index.
WHERE foo LIKE '%bar%';
- 773
64
votes
5
answers
67k
views
How should I index a UUID in Postgres?
I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless ...
- 1,031
58
votes
3
answers
95k
views
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 ...
- 683
57
votes
3
answers
52k
views
What is the optimal data type for an MD5 field?
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).
There is a table names that serves as a sort of central registry. Each row has a ...
- 765
54
votes
1
answer
58k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 643
53
votes
2
answers
59k
views
Does a re-index update statistics?
I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:
Does a re-index update the statistics?
We found discussions online ...
- 645
47
votes
2
answers
79k
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 ...
- 1,104
46
votes
5
answers
79k
views
How to estimate/predict data size and index size of a table in MySQL
I am finding what is the best way to estimate size of a table for that I have studied lot of blogs and forums but unable to find any accurate answer
For example, we have a table City with InnoDB ...
- 9,717
46
votes
1
answer
17k
views
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:
922475bb-ad93-43ee-9487-d2671b886479
There ...
- 713
44
votes
4
answers
57k
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 ' + ...
- 2,707
44
votes
1
answer
90k
views
Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ProofID int NULL
, ...
- 70.3k
43
votes
3
answers
59k
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 ...
- 987
42
votes
4
answers
45k
views
Hard and Fast rule for include columns in index
Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post https://stackoverflow.com/questions/1307990/...
Rocky Singh
42
votes
2
answers
44k
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 one ...
- 721
41
votes
1
answer
37k
views
Need for indexes on foreign keys
I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.
If I have two tables, both of them have an integer as a primary key.
The first table references ...
- 747
40
votes
4
answers
17k
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 ...
Alex
39
votes
6
answers
13k
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 ...
- 805
38
votes
1
answer
80k
views
Index optimization with dates
I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field.
I want to divide the query by millions, for scalability & concurrency purposes, and I ...
- 561
38
votes
5
answers
58k
views
Why are simple SELECTs on InnoDB 100x slower than on MyISAM?
I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.
I copied (description follows) ...
- 563
37
votes
2
answers
29k
views
Index not making execution faster, and in some cases is slowing down the query. Why is it so?
I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down.
The query to create test ...
- 525
36
votes
2
answers
85k
views
Improve performance of COUNT/GROUP-BY in large PostgresSQL table?
I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
- 957
35
votes
7
answers
175k
views
Copy complete structure of a table
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:
select * into dbo.table2 from dbo.table1;
This is just a simple copy of the ...
- 2,009
33
votes
3
answers
42k
views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
- 5,353
33
votes
5
answers
24k
views
Is it bad to have index space larger than data space?
Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space ...
- 461
33
votes
2
answers
17k
views
Custom unique constraint, only enforced if one column has a specific value
Is it possible to have a custom unique 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 I want the ...
- 1,039
33
votes
2
answers
79k
views
CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?
Just came across a weird issue, whereby depending on how I create an index, an index name is required.
http://dev.mysql.com/doc/refman/5.5/en/create-index.html
http://dev.mysql.com/doc/refman/5.5/en/...
- 549
32
votes
1
answer
35k
views
Why would you index text_pattern_ops on a text column?
Today Seven Databases in Seven Weeks introduced me to per-operator indexes.
You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
- 2,328
32
votes
1
answer
36k
views
When to use sort_in_tempdb when rebuilding indexes?
We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (...
- 1,336
31
votes
2
answers
15k
views
Using indexed views for aggregates - too good to be true?
We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.
SELECT
f....
- 920
30
votes
4
answers
8k
views
Do SSDs reduce the usefulness of Databases
I only heard about Robert Martin today, and it seems like he's a notable figure in the software world, so I don't mean for my title to appear as if it's a click bait or me putting words in his mouth, ...
- 499
30
votes
3
answers
26k
views
How to choose a collation for international database?
I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the ...
- 457
30
votes
4
answers
17k
views
Is there a way to show the creation statement for an index in PostgreSQL
I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new ...
- 947
30
votes
2
answers
17k
views
"Recheck Cond:" line in query plans with a bitmap index scan
This is a spin-off from comments to the previous question:
Postgres 9.4.4 query takes forever
Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
- 180k
29
votes
4
answers
6k
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 frequencies:...
- 98.5k
29
votes
2
answers
34k
views
How to create an index to speed up an aggregate LIKE query on an expression?
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 ...
- 527
29
votes
3
answers
25k
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 ...
- 887