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
137 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
2
votes
1answer
107 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
2
votes
1answer
254 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 - ...
2
votes
1answer
583 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
1
vote
1answer
272 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
0
votes
0answers
7 views

Need a persistent, glorified array / bucket storage solution

I'm looking to create a system that logs information ( think, lots of writes, and a few reads ) in order of entry within a key/value datastore. The order precision is absolutely necessary above ...
0
votes
0answers
28 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
0
votes
0answers
27 views

Optimal index wasn't being used until rebuilt

SELECT COUNT(T1.RECID) FROM LEDGERJOURNALTRANS T1 WHERE PARTITION=5637144576 AND DATAAREAID='abcd' AND JOURNALNUM='abcd-00018' This query gets run numerous times when users view a ...
0
votes
0answers
36 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
0
votes
0answers
54 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
0
votes
0answers
18 views

How can I use PostgreSQL's partial sum tree index (if it even exists)? Do any databases allow for use of such an index?

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ...
0
votes
0answers
5 views

Firebird Index Creation Fails

Im creating an index in a table with the following command: CREATE INDEX IDX_CVE_OBS ON FACTF01 (CVE_OBS); But it returns me the following error: this operation is not defined for system tables. ...
0
votes
0answers
61 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
0answers
7 views

indexing table column on the go of mysql query commit

It is said that to optimize the query cost of execution, then it would be by indexing the field of tables in database. Since I don't have a permission to enter the phpmyadmin in my web server as I am ...
0
votes
0answers
43 views

Eager Spool 100%

I have a query like below, when I execute it, the parallel query always takes very long time to finish, I found there is index spool(Eager Spool) which costs 100% in execution plan... How can I ...
0
votes
0answers
93 views

Mongo range query became extremely slow on large collection

I have a collection which has 50 million records in it. After using wired Tiger its size became only 3 GB (2 GB data 700 MB index). I have created index on this collection. But when i do a range query ...
0
votes
0answers
24 views

Can mariaDB contain indices only with data coming from remote tables?

I'm trying to think through a setup with a central mariaDB instance (A) accessing data on physically remote servers (B). All queries to the database (A) can be answered from indices only. Indices are ...
0
votes
0answers
17 views

Granting permission on an altered table in Teradata

I have a table, which has two columns say A and B as part of the Primary index PI_AB. I have to alter the length of B column - increase it. I was planning to alter the primary index to just have A, ...
0
votes
0answers
53 views

Mongo DB accessesNotInMemory issue with RAM size

I am using Mongo Replica set and it seems to be working fine but when i check db.serverStatus i saw that there are lots of accessesNotInMemory for all The DBs in my mongo instance. I checked MY RAM ...
0
votes
0answers
82 views

Duplicate key value violates unique constraint

I'm new to PostgreSQL. I'm currently working on an open source software package which is java based using postgresql 9.3. For some requirement I populated a table (organisationunit) without using the ...
0
votes
0answers
34 views

how to optimize a query with a lot of join

I have the following query: SELECT DISTINCT s0_.id AS id0, s0_.created AS created1, s0_.style AS style2, s0_.styleTitle AS styleTitle3, s0_.styleTitleDescription AS ...
0
votes
0answers
28 views

Mongo find query is not using indexes in java code but working on mongo cosole

I found a strange issue. When i run mongo query from my java code with explain true it does not show me index used in the query but when i run it using mongo console it works fine . ...
0
votes
0answers
33 views

Very small indexes

What is the recommended best practice in dealing with indexes that store very little amount of data (like a few or few hundred pages)? Should these indexes be even maintained? Shouldn't they be ...
0
votes
0answers
34 views

create_index_cost used_bytes vs alloc_bytes

I'm reading a lot about create_index_cost, but I can't seem to find out why there is such a discrepancy between the size of used_bytes and alloc_bytes. I know what each parameter means, but why is ...
0
votes
0answers
40 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
0
votes
0answers
21 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
0answers
56 views

Unique Constraint exception after dropping constraint

We had dropped a unique constraint using the ALTER TABLE DROP CONSTRAINT script, but it is still throwing the Unique Constraint exception while trying to insert. We drilled this down to two possible ...
0
votes
0answers
93 views

Mongodb compound index on two fields where one field is sparse

I have a collection of documents that give stats relating to videos. I have a unique compound index on videoId, date (there is at most one document per video per day). I want to be able to; in a ...
0
votes
0answers
34 views

How best to index large table with many index keys

In our datawarehouse load, we bring data in the following manner. 1. from source to stage 2. stage to a consolidation layer 3. from consolidation layer to operational data store (ODS) ...
0
votes
0answers
29 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
0
votes
0answers
80 views

TEMP Tablespace Exceeded when DBMS_REDIFINITION

Oracle Environment. Table size is 500Gb. Free space left on hard drive - 300Gb. TEMP Tablespace is currently 20GB Task: do the DBMS_Redefinition for the table (partition by month) and optimize ...
0
votes
0answers
59 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
0
votes
0answers
84 views

Does MySQL have a visual tool for suggesting indexes like SQL Server's “Include Actual Execution Plan”?

SQL Server has a remarkably useful feature called "Include Actual Execution Plan" which not only gives you a visual diagram of your query, but also automatically suggests indexes that can improve ...
0
votes
0answers
126 views

Index intersection issue with Mongo 2.6.4

I upgraded my system with 2.6.4 binaries to use specifically index intersection. But when i tried this my DB got down. I created these indexes in my collection "a_1_b_1" : 1.2 GB, "c_-1" : 500 MB ...
0
votes
0answers
64 views

Index on fields with a function

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
0
votes
0answers
36 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
0
votes
0answers
79 views

How to know the correct indexes for mysql?

I tried to find out with explain where I must set the correct index. A current select takes about 20 seconds. Can someone tell me which would be the correct index for my tables? SELECT trade.*, ...
0
votes
0answers
26 views

Creating a table for indexing another table

We have a table with 350m rows and 12 columns, and around 1m rows adding everyday. We have crappy hardware and we cannot add an index on one of the 12 columns that we really really need to. (severe ...
0
votes
0answers
140 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
0answers
57 views

MySql - multi-column index using only null values from one column

I have a Innodb table with a index that looks at 3 columns. ColA is integer and always has a value. ColB is varchar and always has a value. ColC is datetime and sometimes has a value (NULL). My ...
0
votes
0answers
213 views

index usages on table with recursive relationship

we are working on a project that uses jpa and oracle. In this project we have a table with a recursive relation: Create table alarm ( id varchar2(200), ... alarmcode varchar2(50), parent_id ...
0
votes
0answers
419 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 ...
0
votes
0answers
424 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, ...
0
votes
0answers
107 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
403 views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
0
votes
0answers
738 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
0
votes
0answers
122 views

Error creating spatial index sqlserver

I am creating spatial index on sqlserver table. CREATE SPATIAL INDEX [SIndex_udt_cell_SP_GEOMETRY] ON [udt_cell] ( [SP_GEOMETRY] )USING GEOMETRY_GRID WITH ( BOUNDING_BOX =(80.0713, 26.389581, ...
0
votes
0answers
709 views

Is it possible to optimize a range query so an explain statement would state “using index” instead of “using index condition”?

I have a very large table where specific queries made by my application are timing out. The table looks something like this: LargeTable ------------ Id [BIGINT(20)] UtcTimestamp [BIGINT(20)] Pivot ...
0
votes
0answers
73 views

How to/advice for hosting a multi-user database on the cloud with specific objectives

I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the ...
0
votes
0answers
159 views

How implement MySQL unique index with two foreign key that one can be null?

I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null. For example cannot be repeated a null foreign key with the other foreign ...