Questions tagged [database-indexes]
An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data.
586
questions
2883
votes
7
answers
1.1m
views
How does database indexing work? [closed]
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?
For information on queries to index a field, check out How ...
- 83.6k
107
votes
7
answers
38k
views
A list of indices in MongoDB?
Is there a way to see a list of indices on a collection in mongodb in shell? i read through http://www.mongodb.org/display/DOCS/Indexes but i dont see anything
- 12.7k
73
votes
4
answers
63k
views
Can MySQL use multiple indexes for a single query?
Imagine a table with multiple columns, say, id, a, b, c, d, e. I usually select by id, however, there are multiple queries in the client app that uses various conditions over subsets of the columns.
...
- 33.5k
70
votes
1
answer
18k
views
Does rename_column take care of indexes?
Say, we have something like this:
add_column :users, :single, :boolean
add_index :users, :single
and then later we do
rename_column :users, :single, :married
Will ActiveRecord and/or the database ...
- 12.1k
65
votes
1
answer
44k
views
PostgreSQL: Create index for boolean column
I have a table that has one boolean column.
productid integer
isactive boolean
When I execute the query
SELECT productid
FROM
product
WHERE ispublish
LIMIT 15 OFFSET 0
...
- 651
55
votes
3
answers
42k
views
Multiple indexes vs single index on multiple columns in postgresql
I could not reach any conclusive answers reading some of the existing posts on this topic.
I have certain data at 100 locations the for past 10 years. The table has about 800 million rows. I need to ...
41
votes
2
answers
62k
views
Postgres not using index when index scan is much better option
I have a simple query to join two tables that's being really slow. I found out that the query plan does a seq scan on the large table email_activities (~10m rows) while I think using indexes doing ...
- 1,097
34
votes
5
answers
38k
views
Advantage of BTREE?
I create indexes without the USING BTREE clause. Is there any advantage of using BTREE index?
CREATE INDEX `SomeName` USING BTREE ON `tbl_Name`(`column_name`);
- 32k
34
votes
5
answers
12k
views
Django: Does unique_together imply db_index=True in the same way that ForeignKey does?
A field on a model, foo = models.ForeignKey(Foo) will automatically add a database index for the column, in order to make look-ups faster. That's good and well, but Django's docs don't state whether ...
- 56.2k
31
votes
8
answers
43k
views
Do I need to add an Index on ORDER BY field?
I have a query of such like
$query = "SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time";
Do I need to add an index on the comment_time field?
Also, if I want to get the data between ...
- 3,631
31
votes
1
answer
20k
views
Why are super columns in Cassandra no longer favoured?
I have read in the latest release that super columns are not desirable due to "performance issues", but no where is this explained.
Then I read articles such as this one that give wonderful indexing ...
- 18k
28
votes
3
answers
31k
views
Cassandra: choosing a Partition Key
I'm undecided whether it's better, performance-wise, to use a very commonly shared column value (like Country) as partition key for a compound primary key or a rather unique column value (like ...
- 22.2k
27
votes
2
answers
19k
views
How do I know when to index a column, and with what?
In docs for various ORMs they always provide a way to create indexes, etc. They always mention to be sure to create the appropriate indexes for efficiency, as if that is inherent knowledge to a non-...
- 56.2k
25
votes
3
answers
20k
views
Neo4j: Step by Step to create an automatic index
I am creating a new Neo4j database. I have a type of node called User and I would like an index on the properties of user Identifier and EmailAddress. How does one go setting up an index when the ...
- 23.8k
24
votes
3
answers
22k
views
Indexes with included columns, what's the difference?
I've never really understood the difference between these two indexes, can someone please explain what the difference is (performance-wise, how the index structure will look like in db, storage-wise ...
- 649
24
votes
1
answer
98k
views
SQL Server Web vs Standard edition
I have found out that there's two versions of SQL Server types that are very different in terms of pricing...
The Web version from my host provider costs about 13$ per 2 core packs, whereas the ...
- 3,753
22
votes
1
answer
10k
views
Deferrable, case-insensitive unique constraint
Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive?
Let's assume the following basic table:
CREATE TABLE sample_table (
my_column ...
- 7,225
21
votes
1
answer
18k
views
How to implement composite primary keys in rails
I have a User model that looks like this:
class User < ApplicationRecord
belongs_to :organization
belongs_to :department
end
The users table in the database has the two foreign keys ...
- 10.4k
19
votes
4
answers
32k
views
Creating Indexes on DB with Hibernate @Index Annotation
I have annotation-driven hibernate capabilies on my project.
Now I want to create an index over a column. My current column definition is
@NotNull
@Column(name = "hash")
private String hash;
and I ...
- 44.1k
14
votes
1
answer
25k
views
Indexes on join tables
When searching on Google for join table indexes, I got this question.
Now, I believe that it is giving some false information in the accepted answer, or I do not understand how everything works.
...
- 899
12
votes
1
answer
15k
views
Row level security(RLS) performance is significantly slower in postgres.
Description :
Here is the sample demonstration of the performance issue.
We first created two tables , enabled row level security and created policy as well .
Table definition:
create table ...
user4029350
11
votes
4
answers
32k
views
Different types of database indexes?
I am trying to compile a list of non-system-specific database indexes. I've looked at Oracle, DB2, MySQL, Postgres and Sybase, and almost every resource has a different list. So far I have seen:
...
- 1,649
10
votes
3
answers
20k
views
Oracle - Create an index only if not exists
Is there any way to create indexes in oracle only if they don't exists ?
Something like
CREATE INDEX IF NOT EXISTS ord_customer_ix
ON orders (customer_id);
- 18.5k
10
votes
4
answers
19k
views
How can I quickly detect and resolve SQL Server Index fragmentation for a database?
I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 ...
- 411
10
votes
5
answers
19k
views
SQL Server - INSERT failed because of 'ARITHABORT'
I use NHibernate and SQL Server 2005 and I have an index on a computed column in one of my tables.
My problem is that when I insert a record to that table I get the following error:
INSERT failed ...
- 509
10
votes
1
answer
5k
views
How do I change a column from null to not null when an index depends on that column without recreating the index?
I have a column Column which is declared to be NULL DEFAULT(GETUTCDATE()) and there's a non-clustered index that includes this column. I want to change this column to be NOT NULL DEFAULT(GETUTCDATE()) ...
- 169k
10
votes
2
answers
1k
views
Why MongoDB doesn't use Index Intersection?
I am trying to reproduce the first example of index intersection instruction (http://docs.mongodb.org/manual/core/index-intersection/) but facing a problem: mongo doesn't uses both indexes
My steps:
...
- 550
9
votes
2
answers
3k
views
How to delete composite indexes in Cloud Firestore?
The documentation says :
https://firebase.google.com/docs/firestore/query-data/indexing
To delete an index:
Go to the Cloud Firestore section of the Firebase console.
Click the Indexes tab.
Hover ...
- 612
9
votes
1
answer
14k
views
How to show all invalid objects in PostgresQL
Looking for the view I can list up all 'invalid' objects in PostgreSQL.
In Oracle, we can use dab_objects.status column but I'm not sure if there is a simple way to do such a thing in PostgreSQL.
...
- 927
9
votes
3
answers
14k
views
How binary search is used in database indexing
I know how binary search works but I wanted to know practical uses for binary search...
I searched through the internet and I found that the main use is data base indexing, but I couldn't understand ...
- 111
9
votes
1
answer
4k
views
restoring table with pg_restore does not include primary key or indexes
So I made a backup of a table using pg_dump:
pg_dump -U bob -F c -d commerce -t orders > orders.dump
This table had several listed indexes such as a primary key
However when I restore this table ...
- 91
8
votes
4
answers
7k
views
Cannot remove an index from table in Rails 4 and PSQL 9.3
In my schema.rb I have the following line:
add_index "users", ["email"], name: "index_users_on_email", unique: true, using: :btree
When I run \di in psql I get:
Schema | ...
- 23.9k
8
votes
2
answers
5k
views
Are POSIX' read() and write() system calls atomic?
I am trying to implement a database index based on the data structure (Blink tree) and algorithms suggested by Lehman and Yao in this paper. In page 2, the authors state that:
The disk is ...
- 19.5k
8
votes
1
answer
4k
views
mysql - how to run query without index
I am looking to compare indexes and further optimize my code. What I would like to do is force the query to run without an index so I can see what difference it has made. Is it possible to do this?
- 11.8k
8
votes
1
answer
9k
views
Do indexes make db updates slower?
It is usually said when you create indexes, it makes fetches faster for you but on the same time they slow down your updates (delete,insert,update) as with every update indexes are to be recreated.
...
- 2,150
8
votes
1
answer
145
views
MySQL query by date with big inverval
I have big table with 22 millions records.
I want to execute next query:
select auto_alerts from alerts_stat where endDate > "2012-12-01"
To improve performance I added BTREE index for endData ...
- 5,304
7
votes
3
answers
13k
views
Why would MySQL use index intersection instead of combined index?
From time to time I encounter a strange MySQL behavior. Let's assume I have indexes (type, rel, created), (type), (rel). The best choice for a query like this one:
SELECT id FROM tbl
WHERE rel = 3 ...
- 1,676
7
votes
2
answers
1k
views
Is it possible to do usual atomic INSERT operation but update Indexes asynchronously?
indexes make read fast but write slower. But why can't you have single writes and have db add indexes asynchronously with time, also cache in the INSERT until it's indexed?
Is there any database like ...
- 17.6k
7
votes
1
answer
10k
views
MySQL Optimization: EXPLAIN "Extra" column contains "Using Where"
So I always thought that seeing "Using Where" in the Extra column was a good thing. However, I was planning a brown bag lunch for my coworkers on intepreting EXPLAIN and now I'm not so sure. The MySQL ...
- 13.4k
7
votes
2
answers
85
views
Creating indexes on columns where primary key or unique key constraints already present
I am currently learning indexes in Oracle.
In Oracle documentation, following can be found :
Although the database creates an index for you on a column with an integrity constraint, explicitly ...
- 4,154
7
votes
1
answer
3k
views
Why PostgresQL count is so slow even with Index Only Scan
I have a simple count query that can use Index Only Scan, but it still take so long in PostgresQL!
I have a cars table with 2 columns type bigint and active boolean, I also have a multi-column index ...
- 93
6
votes
6
answers
494
views
How can I speed up queries against tables I cannot add indexes to?
I access several tables remotely via DB Link. They are very normalized and the data in each is effective-dated. Of the millions of records in each table, only a subset of ~50k are current records.
...
- 8,841
6
votes
4
answers
6k
views
How to remove index varchar_pattern_ops in a django (1.8) migration?
When creating a model with a models.varchar(...) field, a varchar_pattern_ops index is being created.
This is the table generated in postgresql
Table "public.logger_btilog"
...
- 7,078
6
votes
1
answer
2k
views
Why PostgreSQL doesn't use trigram index
I have created a trigram index in order do execute a query with a 'like %text%' condition, but PostgreSQL 9.6 doesn't use the index to perform the query.
CREATE EXTENSION pg_trgm;
CREATE INDEX ...
- 2,391
6
votes
2
answers
8k
views
Do non-clustered indexes slow down inserts?
I'm working in Sql Server 2005. I have an event log table that tracks user actions, and I want to make sure that inserts into the table are as fast as possible. Currently the table doesn't have any ...
- 6,680
6
votes
2
answers
2k
views
Are UNIQUE SQL indexes also used for speeding up searches?
Suppose a SQL database of "values of series". Each Value belongs to a Series, and has a date:
@Entity
class Series { … }
Value {
Series series;
Date date;
…
}
Each value is unique for ...
- 30.1k
6
votes
2
answers
3k
views
Cassandra 1.1 storage engine how does it store composites?
I'm trying to understand Cassandra's storage engine when it comes to composite columns. Unfortunately, the documentation I've read so far contains errors and is leaving me a bit blank.
First, ...
- 18k
6
votes
2
answers
779
views
What is the most optimal index for this delayed_job query on postgres?
delayed_job does a query like this regularly:
SELECT "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2012-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2012-05-23 11:16:...
- 22.7k
5
votes
1
answer
5k
views
Putting an index on a date field in MySQL
Is there going to be any real benefit to me putting indexes onto date fields that are going to be mainly used in queries using stuff like.
dateField < 'var'
And
'var' BETWEEN dateField1 AND ...
- 8,692
5
votes
2
answers
1k
views
Is a single-column index needed when having multicolumn index?
I've been dropped in a system that was poorly designed. Now I'm doing DBA on their DB, and I have a lot of situation like the following (Pseudo-code):
Table t1{
c1;
c2;
c3;
c4;
key(c1);
key(c2);
key(...
- 79