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)

3
votes
1answer
167 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 ...
0
votes
0answers
20 views

Questions about linear hashing

I've a little confused about linear hashing index mechanism, does anyone can explain that in the algorithm format. And does the sequence of different insertions into that hash table matters?
2
votes
2answers
117 views

Most performant way to fetch last record

I have a Postgres table Prices with the columns price (Decimal) product_id (Int) Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
0
votes
0answers
37 views

I need advise on if I should rebuild my index [duplicate]

I have a SQL Server 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my ...
0
votes
0answers
108 views

Based on these DBCC SHOWCONTIG stats, should I index this table?

I have a SQL 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most ...
1
vote
1answer
36 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
1
vote
1answer
106 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
1
vote
1answer
35 views

Rebuild the Fulltextcatalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this 30 hours. Our business users ...
4
votes
0answers
99 views

Rebuild Index Task removed WHERE clause from filtered index [closed]

I have a filter index setup on a table as follows: CREATE UNIQUE NONCLUSTERED INDEX [IX_TableName_Column_Filtered] ON [Schema].[TableName] ( [Column] ASC ) WHERE ([Column] IS NOT NULL) WITH ...
0
votes
0answers
226 views

Yet another slow query Postgresql 9.3

I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
1
vote
1answer
42 views

What indexes would I create for a query like this?

Here's a query I've got: SELECT "balance_transactions"."fee" FROM "balance_transactions" JOIN charges ON balance_transactions.source = charges.balance_id WHERE "balance_transactions"."account_id" = ...
0
votes
1answer
57 views

Optimize query to minimize tmp copying time

I have this mega query : SELECT s.eleve_id, crs.id_cours as 'cours_id', mc.code as cours_code, s.employe_id, s.id_seance, '0' as theorique, s.statut, s.type_vehicule_id, s.type_conduite_id, ...
3
votes
0answers
145 views

SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database. Part of the optimisation requires persisting ...
1
vote
1answer
328 views

Retrieve Data faster from a huge Oracle Table

I have a huge Oracle table ( ttdinv700 ) which contains almost 20 million rows. I am trying to execute the below query, on the table: select max(ttdinv700.t$trdt) from ttdinv700, ttiitm001 ...
1
vote
1answer
27 views

Request shown in slow logs

In my MYSQL configuration I activated logging of request not using indexes. slow_query_log=1 slow_query_log_file = /home/mysql/sqlprive.log long_query_time=7 log_queries_not_using_indexes =1 But ...
3
votes
3answers
216 views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select ...
0
votes
2answers
199 views

Unusable index on PK - Oracle DBMS

We are executing a large number of INSERT operations (140k circa) on a table during an ETL process and this error pops out: Caught SQLException for insert into DMALM_USER_ROLES [...] Caused by: ...
1
vote
0answers
52 views

SQL Server Full-Text Index with Stoplist

I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL ...
1
vote
0answers
50 views

What's Scalar Operator in the plan?

I have a doubt. What means this Scalar Operator in the print below? I've been investigating our database cause we have severals RESOURCE_SEMAPHORE_QUERY_COMPILE . I've seeing a lot of queries using ...
2
votes
0answers
78 views

Disabled index still incurs overhead when inserting

I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still ...
5
votes
1answer
96 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
1
vote
2answers
797 views

Is it possible to index a computed column used in ORDER BY?

I am having problems with my mysql server, because of not properly written queries. I did not use indexes, because I do not know how to add them on computed columns, in a query that has SELECT ...
2
votes
0answers
85 views

Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a ...
-1
votes
1answer
552 views

How to reduce the size of index file in SQL Server 2008? [closed]

I am accessing two servers Application server (WC) Database server The application server is using the database server Here index file in the database server is growing automatically without any ...
1
vote
1answer
52 views

why would rebuild index reduce btree leaf numbers

Rebuilding an index might reduce the number of leaf nodes by about 20% - 30%. The most you can possibly expect from this reduction is 20%-30% for very expensive operations like a FULL INDEX SCAN. ...
2
votes
2answers
96 views

fragmentation for non cluster index growth very fast to 30

i have table with about 7 milion records.index fragmentation was about 90 . i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index ...
6
votes
1answer
361 views

Why is multiple independent index always better than concatenated index [closed]

Index Merge: Combining Multiple Indexes It is one of the most common question about indexing: is it better to have one index for each column or a single index for all columns of a where ...
0
votes
1answer
192 views

Why are InnoDB's index keys limited to 767 bytes?

MySQL's official documentation states about InnoDB: An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. The meaning of this ...
1
vote
1answer
90 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
385 views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
0
votes
0answers
45 views

What happens when an SSH session is terminated while a query is running?

I have a huge database on a dedicated server, whenever trying to modify the db I use PUTTY to establish an SSH connection and then do the work. Yesterday I started an INDEX query on an innodb table ...
2
votes
1answer
296 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
1
vote
1answer
111 views

How to find MongoDB index statistics

Is there any way we can figure out what all indexes are cached into RAM ? I want to know page related stats for individual indexes (# of hits and miss )
1
vote
1answer
86 views

how does multiple column index work in details

if we have a table T1 with columns A,B,C,D,E and an index ( A,B,C) built for it if we a SQL query joining on columns A,B or A,B,C or A, this index can still be used, but if the query is joining on B ...
-1
votes
1answer
216 views

Will the indexing affect insert/update/delete operation in Mysql [closed]

Will the indexing affect insert/update/delete operation in Mysql 5.1 Will it take down performance of insert update delete operations, If yes, why it will happen so. So are indexes always meant for ...
1
vote
2answers
129 views

Indexing varchar column to search for first letter

I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character. The query to optimize is: SELECT oggetto.id_oggetto, titolo, anno, ...
1
vote
1answer
71 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 ...
0
votes
1answer
99 views

Is ANALYZE useful immediately after creating an InnoDB index?

Let's create a new index on an existing table: CREATE INDEX `IDX_some_index` ON `some_table` (`a_column`, `another_column`); Is this useful? ANALYZE TABLE `some_table`; Can anyone point to ...
0
votes
1answer
287 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
1
vote
1answer
170 views

UUID in mysql database

I have a problem, I have two servers where I have to save my data. For persistent data I run mySql inno DB. I have a lot of entries, so I need fast (CRUD operations). For indexing I was thinking to ...
1
vote
1answer
177 views

Postgres - Optimizing an view dependent on an aggregate function?

I have a table of about 15 million records. Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes. This results in multiple rows ...
4
votes
1answer
146 views

Database having only db_datareader shows invalid properties for a index

If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in ...
-1
votes
1answer
213 views

Difference between Clustered Index and Unique Clustered Index? [closed]

I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index? AFAIK creating clustered index can be done for a ...
0
votes
1answer
36 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
4
votes
2answers
93 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...
0
votes
1answer
128 views

Should I index before or after importing a dump?

I am using mongodb to persist a very big-size (90G), which has nearly 40,000,000 items. I read and parse this file and insert all items into mongodb (my programming language is perl, batch_insert, and ...
3
votes
1answer
83 views

How many blocks is my database

How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)
1
vote
2answers
454 views

What is index fragmentation percent a percentage of?

So I am familiar with the concept of index fragmentation and how to fix it but I'm not really sure of one specific item: When you look at index fragmentation, it's always reported in terms of ...
1
vote
0answers
116 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
2
votes
1answer
557 views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...