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)

0
votes
0answers
617 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
50 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
67 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, s....
4
votes
0answers
345 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
1k 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
37 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
2k 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 cast(left(cast(...
0
votes
2answers
959 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: java....
2
votes
0answers
106 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
56 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
137 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 ...
6
votes
1answer
210 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 > ...
2
votes
2answers
3k 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 COUNT(*)...
3
votes
1answer
507 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
2k 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
58 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
293 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
446 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
561 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 ...
3
votes
2answers
244 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 ...
8
votes
1answer
8k 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 ...
4
votes
1answer
1k 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 ...
6
votes
1answer
819 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
707 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
2k 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
642 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
93 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
393 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 ...
1
vote
1answer
2k 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
425 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
1k 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 ...
5
votes
1answer
949 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
vote
1answer
5k 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
55 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
113 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): ...
1
vote
1answer
639 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
149 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
3k 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
164 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 ...
5
votes
1answer
3k 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 ...
4
votes
2answers
7k views

Adding index to large mysql tables

I have a table | base_schedule_line_items | CREATE TABLE base_schedule_line_items ( id int(10) unsigned NOT NULL AUTO_INCREMENT, installment int(10) unsigned NOT NULL, on_date date ...
1
vote
0answers
745 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
5
votes
1answer
784 views

Don't rebuild indexes < 1000 pages - then why bother having them?

Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM). If I'm not rebuilding them, ...
10
votes
3answers
213 views

One index or two?

I have the following index created on a table in my database: CREATE INDEX [idx_index1] on [table1] (col1, col2, col3) The server is suggesting the following 'missing' index: CREATE INDEX [...
0
votes
1answer
156 views

Index being ignored by Oracle

I have a table for sensor data, simplified it looks like this: DEVICE_ID NUMBER SENSOR_ID NUMBER DATA_TIME NUMBER DATA_VALUE1 NUMBER The table constains billions of rows and is partitioned by week ...
1
vote
4answers
250 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
10
votes
1answer
4k 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 ...
8
votes
1answer
8k 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 ...
0
votes
1answer
406 views

Which index types do not support online index rebuilds?

In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds? and why? and which activity-operations can prevent online index rebuilds?
0
votes
1answer
1k views

What is blocking 'SELECT… WITH(NOLOCK)'?

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: SELECT Quantity FROM [Production].[TransactionHistory] WITH(NOLOCK) while executing a rebuild ...