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
0answers
9 views

mysql add index [on hold]

how can i add index for this query: SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), INTERVAL 120 SECOND), 'Y', 'N') IS_ONLINE, ...
0
votes
1answer
29 views

Index planning for many columns with different usage

I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million ...
1
vote
1answer
162 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not ...
7
votes
1answer
100 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
0
votes
1answer
101 views

Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)

We have a huge query on an Informix database that is not performing well. The query is doing a join of 9 different tables. Maximum row count is just 40,000 rows. The query also has 9 sub-queries, and ...
6
votes
1answer
72 views

Guidelines for full-text index maintenance

What guidelines should be considered for maintaining full-text indexes? Should I REBUILD or REORGANIZE the full-text catalog (see BOL)? What is a reasonable maintenance cadence? What heuristics ...
0
votes
1answer
24 views

Redundant to have index + full text on same column?

If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to ...
4
votes
1answer
59 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
0
votes
1answer
19 views

MongoDB 3.0 Indexes maximum limitation

I have a mongo DB that store user's public messages. Each message should be categorized by at least one TAG (3 max in an array) The TAG can be anything up to 32 characters. ([0..9a..z]) the Tag ...
5
votes
4answers
6k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
0
votes
1answer
37 views

Oracle - ORA-01654: unable to extend index in tablespace

I referred to the below related question, but it was not helpful in my case: ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace> I ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
1
vote
1answer
29 views

Should I add a clustered Index on a foreign key that is not unique?

I have access to a table that has a primary key unique clustered index on an nvarchar(36). I need to create another table that hangs our own data off that unique clustered index in another table and ...
2
votes
2answers
436 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 ...
5
votes
2answers
151 views

“Recheck Cond:” line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
1
vote
0answers
13 views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
2
votes
2answers
31 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
0
votes
2answers
118 views

Will reindexing use the empty space or just grow table?

I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space ...
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, ...
2
votes
2answers
34 views

avg_page_space_used_in_percent and fill factor

Trying to find an answer to this one but struggling. sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent. How does this fit in with the fill factor of an index? For ...
2
votes
2answers
183 views

where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ? it will stay with that table and add a new ...
16
votes
6answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
4
votes
3answers
120 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
0answers
201 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
0
votes
1answer
57 views

How does Postgres make its B-tree index?

I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size. PostgreSQL manual about the index here ...
7
votes
3answers
276 views

Index tuning question

I am tuning some indexes and seeing some issues would like to take your advice On 1 table there are 3 indexes dbo.Address.IX_Address_ProfileId [1 KEY] ProfileId {int 4} Reads: 0 Writes:10,519 ...
0
votes
1answer
28 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
0
votes
1answer
144 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
1
vote
2answers
118 views

Add UNIQUE index fails with duplicate entry error, but no duplicates found

Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT ...
0
votes
1answer
34 views

Creating index for large table is taking forever

I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012 Enterprise edition. I know how to create index but its taking forever and would like ...
0
votes
4answers
476 views

Mysql - How to Optimize Retrieval Time in a Table

I have query which has 200 million Records in a single table. I am using BTree indexes in my table: mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3; ...
8
votes
1answer
280 views

Why would function based indices I’ve created lower the cost but not show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was OK to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
1
vote
1answer
36 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...
5
votes
3answers
417 views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
4
votes
1answer
76 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 ...
11
votes
2answers
745 views

Why does my WHERE clause benefit from an “included” column?

According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index. I have this definition: CREATE TABLE [dbo].[JobItems] ( ...
3
votes
2answers
60 views

MySQL indexes maintenance

I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries. I am familiar with that formula that calculates the ...
3
votes
0answers
100 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
0
votes
1answer
151 views

Use index intersection feature in mongo 2.6 to compound shard key

I am having three indexes like name,age,salary. I can use them as compound index as per 2.6 (name and age together). But can i use the same indexes as compound shard key like name and age. i also have ...
5
votes
1answer
70 views

What do the shared buffers metrics for various types of index scans represent?

TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages ...
2
votes
2answers
372 views

Check if an index exists by comparing columns

We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order. ...
3
votes
1answer
489 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
3
votes
1answer
65 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 ...
0
votes
1answer
15 views

PostgreSQL: Find rows for which any index of any value contained is over a given size

I have a database originally built with an instance of PostgreSQL with a custom page size much larger than the default. I'm trying to migrate its contents to a standard, 8k-page database. Because ...
5
votes
2answers
503 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
6
votes
1answer
79 views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ...
1
vote
0answers
8 views

Access algorithms in Partial Index implementation of PostgreSQL 9.4?

I am studying big-O time-space complexities of different algorithms with the partial index of PostgreSQL 9.4: introsort (O(n log n)-time, O(log n)-space) and heapsort (O(n log(n))-time ...
6
votes
3answers
119 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
2
votes
1answer
353 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
1
vote
1answer
45 views

Dropping Foreign Key In Production Environment

I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, ...