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)

1
vote
2answers
636 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
76 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
454 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
50 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
91 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
356 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
177 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
82 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
251 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
44 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
207 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
95 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
81 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
181 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
112 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
91 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
256 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
147 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
165 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
132 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
143 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
35 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
91 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
116 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
79 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
396 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
454 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 ...
1
vote
2answers
435 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 ...
0
votes
0answers
50 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?
0
votes
0answers
46 views

Group by clause giving temporary and filesort

I am using this simple select query which selects data from table inner joins and using group by clause. Here is the query ..I have defined indexes for the where clause and group by clause. But some ...
5
votes
1answer
278 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, ...
9
votes
3answers
178 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
76 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 ...
0
votes
2answers
136 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 ...
4
votes
1answer
629 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 ...
2
votes
1answer
429 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
76 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
189 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 ...
0
votes
1answer
72 views

Oracle 11g INDEX_SS Hint

Given a table with columns A, B, and C of type CHAR(15) and an unique index If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I ...
2
votes
1answer
369 views

mysql “Lock wait timeout exceeded” two transaction with different keys indexed

I have set of tables that I need to delete their records with a specific key and insert new records. Different transactions are used one for each key: Transaction 1: DELETE FROM table1 WHERE id=1; ...
0
votes
1answer
60 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
1
vote
1answer
62 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
1
vote
2answers
413 views

Postgresql indexes in memory

I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes ...
2
votes
1answer
137 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
2
votes
1answer
265 views

MySQL InnoDB issue not using indexes correctly [Percona 5.6]

I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good My old server is a bit less powerful, and was running ...
-8
votes
1answer
107 views

What is the structure that SQL Server uses to build and maintain indexes called?

What is the structure that SQL Server uses to build and maintain indexes called?
1
vote
1answer
73 views

Why add indexes to these columns in the Database?

I am trying to get my head around this code. It's from the Rails Tutorial Book and is part of the process of making a twitter like application. class CreateRelationships < ...
1
vote
0answers
19 views

Why is SQL Server Recommending I Create this Index? [duplicate]

I have the following query: Select SubId, Max(ReadTimeLocal) From dbo.PanelWorkflow Where ProcessNumber IN ...