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)

2
votes
1answer
432 views

Copy Postgres databases with indexes between instances

How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes? We have an application we deploy which uses Postgres 8.4.7 database in Linux. ...
5
votes
3answers
677 views

Database Indexing - Maintenance Jobs

I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
1
vote
1answer
138 views

Simple Slow Query Not Using Indexes

I have a slow query running on a large forum. The EXPLAIN synopsis: mysql> EXPLAIN SELECT COUNT(post_thanks.id) as thanked, post_thanks.postid, post.title, post.dateline FROM ...
3
votes
2answers
700 views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
1
vote
1answer
106 views

Partitions and Indexes, in which cases should be used

I know some knowledges: Partitions used to achieve better performance (like described in this article) And indexes used to increase performance in selection operations. And as result my question: ...
2
votes
1answer
784 views

Index with multiple leaf levels

I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
12
votes
1answer
7k 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 ...
7
votes
1answer
1k views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
4
votes
1answer
1k views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
3
votes
1answer
123 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
25
votes
2answers
8k 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? ...
4
votes
5answers
284 views

How much does an index need to narrow the results of a search in order to be useful?

How much does an index need to narrow the results of a search in order to be useful in speeding up queries? Some examples all across the spectrum: A column for storing true/false values obviously ...
2
votes
1answer
510 views

Error creating index on PostgreSQL 9.1

I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process. Is it ...
4
votes
3answers
4k views

SQL Server Timeout: Drop and Recreate Index Solves the Issue

After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having. The strange thing is ...
0
votes
1answer
346 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
4
votes
1answer
266 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
1
vote
1answer
761 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
2
votes
1answer
2k views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
2
votes
1answer
158 views

Combining columns in index

With reference to RolandoMySQLDBA's answer on MySQL: Index when joining to tables not being used (Performance optimizing question) He mentioned Make sure you build an index that involves the ...
1
vote
1answer
827 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
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=# ...
2
votes
2answers
6k views

Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?

I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
1
vote
1answer
339 views

Index strategy for a Queue Table, getting rid of filesort

I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
4
votes
2answers
1k views

Why index REBUILD does not reduce index fragmentatation with a page count > 1000?

This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found ...
1
vote
1answer
230 views

What would be a optimal index for this query in mysql

I'm trying to optimize this query. SELECT * FROM sales_reps WHERE account_id = 1 AND (state = 'enabled') AND (clients >= 5 OR revenue >= 10000) ORDER BY name, platform, client_group, ...
4
votes
1answer
713 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
1
vote
2answers
52 views

Do I need an index on this?

I have a sql server db and have a question regarding foreign key indices. Here's a snippet from the db: In programme_affiliates, I have a computed field, this is always set to '2' in this ...
3
votes
2answers
600 views

Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?

I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it. I have a very big table with something like ...
3
votes
1answer
512 views

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables. While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
4
votes
2answers
3k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
4
votes
1answer
4k views

Indexing SQL Server datetime Columns for Performance

Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column? If yes, should the index ...
5
votes
2answers
887 views

Should I rely on Database Engine Tuning Advisor for creating indexes?

How should I rely on Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index ...
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 ...
9
votes
2answers
283 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
1
vote
1answer
167 views

Finding exact row in Clustered index leaf page

When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page). Every data page has row ...
1
vote
1answer
240 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
16
votes
2answers
19k 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 ...
5
votes
1answer
2k views

How are multiple indexes used in a query by MySQL?

Imagine a table where there are separate indexes on columns a and b. The query runs somewhat like this: SELECT col1, col2 FROM `table` WHERE a = 'value_1 AND b = 'value_2'; Now, does this query ...
3
votes
1answer
288 views

For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns? Like if I have this table: CREATE TABLE [dbo].[tbl_shipping_boxes] ( ...
4
votes
1answer
838 views

Does creating an index on BIT(n) attribute in MySQL make sense?

I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2. I've seen some mentions on the internet that BIT ...
-2
votes
1answer
5k views

What are performance benefits when using number type instead of using char/varchar?

From various posts, I have summarized about using number type instead of char/varchar for PK: If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
2
votes
1answer
191 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
1
vote
2answers
702 views

Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?

everything is in the question. I have a table named tableA on which we have indexes and foreign keys and constraints. I have to modify content in a column called column1. This being a production ...
0
votes
1answer
57 views

Why $in is much faster then $all

I am well aware of what those 2 do. $in take .3 seconds. And $all take 26 seconds. Huge different. If anything $in should take longer because it returns more data. Also if the limit is removed, $in ...
3
votes
1answer
438 views

Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

I am not sure if this question has been asked or not. At least I couldn't find it. I am curious about a primary key in terms of efficiency with data searching and retrieval. This is a hypothetical ...
14
votes
3answers
3k 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 ...
1
vote
1answer
1k views

How to interpret this “db.explain()” for this mongodb query

db.tablebusiness.find({ "LongitudeLatitude" : { "$within" : { "$centerSphere" : [[106.772835, -6.186753], 0.0089967464101566] } }, "indexContents" : { "$all" : ["warung"] }, "Prominent" : { "$gte" : ...
4
votes
1answer
820 views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
5
votes
2answers
3k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
8
votes
2answers
8k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...