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)

4
votes
1answer
480 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
4
votes
1answer
615 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
2answers
6k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
3
votes
1answer
136 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
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
444 views

space usage on sys.allocation_units and sp_spaceused

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a ...
2
votes
2answers
436 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 ...
1
vote
1answer
228 views

Recommended Postgres settings to speed up indexing on a 100GB database?

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk. I will be using this server to serve both my site and my database (i.e. it's not just a database server). ...
0
votes
1answer
353 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
12
votes
1answer
9k 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
4answers
1k 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. ...
6
votes
1answer
220 views

Is there any reason to use index for very small tables (up to 1000 rows)?

During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, ...
5
votes
1answer
2k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as ...
5
votes
2answers
567 views

Optimizing IP Range Search?

I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query. In my table I have 3 columns CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
5
votes
3answers
259 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
77 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
4
votes
3answers
87 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
4
votes
1answer
84 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: ...
4
votes
1answer
321 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
4
votes
0answers
638 views

SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource? The exclusive locks were on the primary key index for ...
4
votes
2answers
2k views

Index before or after bulk load using load infile?

I have a database with over 1B rows and two columns that are indexed (in addition to the PK). Is it better to have the index pre-defined in the table before the load infile or better to index after ...
4
votes
1answer
1k views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
4
votes
1answer
1k views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
4
votes
1answer
209 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
1answer
68 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON ...
3
votes
1answer
84 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
3
votes
1answer
747 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
3
votes
2answers
190 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
3
votes
1answer
631 views

Error when creating an index in SQL Server 2012

I'm trying to create the following index on our test environment (SQL Server 2012): CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction] ( ...
3
votes
3answers
1k views

Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included. Is ...
3
votes
1answer
210 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 ...
2
votes
3answers
232 views

Why does PK constraint require a separate index when I already have a suitable unique clustered index?

This is part of table definition from here: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY ...
2
votes
1answer
57 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
2
votes
2answers
109 views

Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

I am using PostgreSQL 9.4.4. I have a query like this: SELECT COUNT(*) FROM A,B WHERE A.a = B.b a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b By default, ...
2
votes
1answer
49 views

Slow query on mid-size tables with good index coverage

I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only ...
2
votes
2answers
129 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
2
votes
1answer
2k views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
2
votes
1answer
3k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
2
votes
1answer
862 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
2
votes
1answer
72 views

Do I need to reenter old data after adding an index to a table?

I want to add index to my tables. Some of my tables already have couple thousand rows. Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
2
votes
2answers
1k views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
1
vote
0answers
29 views

Finding missing_index_details in MySQL

MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL? This article (scroll to "Find missing indexes") says that in Microsoft SQL ...
1
vote
2answers
144 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...
1
vote
1answer
102 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
1answer
125 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
1
vote
1answer
117 views

Is database record access sequential after retrieving the indexed page from disk? [closed]

This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer. What I understand about databases that if a queried or requested record is found in the ...
1
vote
0answers
128 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
2answers
126 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, ...
1
vote
1answer
157 views

Optimizing Postgres query

I've got a one to one relation from users to addresses table. Where one user can have one search address and one verified address. I've got two indexes on addresses table : Index on state field ...
1
vote
1answer
83 views

Is an explicit KEY redundant if it is covered by a UNIQUE KEY?

I am auditing an application which I've been developing over the course of several weeks. I've noticed this in CREATE TABLE: UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`), KEY `baz` (`baz`), Is the ...