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
2answers
35 views

GIN index not used when adding order clause

I'm trying to speed up a query that executes three ILIKE queries and reduces these with or (returning the overall count and 10 entries) SELECT *, count(*) OVER() as filtered_count FROM "users" ...
3
votes
1answer
54 views

missing indexes on distributor database

Looking for missing indexes on my distributor databases, I surprisingly find too many. the tables that need to be indexed are generally 3: MSrepl_commands MSlogreader_history ...
4
votes
1answer
88 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: ...
1
vote
2answers
30 views

DB select query performance when queried by partial key of primary key [on hold]

My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, ...
2
votes
1answer
164 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...
9
votes
3answers
315 views

Included Column versus Filtered Index

We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes). 10 to 15% of the rows are soft-deleted rows (DeletedDate is not ...
1
vote
2answers
53 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
1
vote
2answers
34 views

MySQL - Small table popping up in slowlog, refuses to use index

So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes). I tested using a large multipart index (that's just ...
2
votes
0answers
12 views

Slow fulltext search for terms with high occurence

I have a table that contains data that is extracted from text documents. The data is stored in a column called CONTENT for which I have created this index using GIN: CREATE INDEX "File_contentIndex" ...
7
votes
2answers
136 views

Should I mark a composite index as unique if it contains the primary key?

Given some table with a primary key, e.g.: CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, FirstName nvarchar(50), LastName nvarchar(50), Address nvarchar(200), Email ...
1
vote
2answers
58 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 ...
3
votes
2answers
85 views

Can I improve the performance of this MySQL query by adding an index?

I ran SET GLOBAL slow_query_log = 'ON'; in MySQL and this appeared in the log: SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), ...
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
164 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 ...
9
votes
1answer
151 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 ...
7
votes
1answer
80 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
29 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
61 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
22 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
41 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
30 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
445 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
161 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
32 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
122 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
88 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
35 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
187 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
58 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
280 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
30 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
145 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
130 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
36 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
478 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
37 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
423 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
77 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
746 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
63 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
101 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
153 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 ...