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
1answer
132 views

How to manage RAM consumption in MongoDB?

I am using MongoDB. I have a hardware device which continuously submits data to the database every 10 seconds for 4 hours a day. The data includes latitude, longitude, and other basic information of ...
1
vote
2answers
2k 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 ...
1
vote
1answer
33 views

Sql Server 2008 : What is the cost of accessing an index?

I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states : Accessing the index takes time, and consumes ...
1
vote
1answer
98 views

Why is MariaDB 10 refusing to use my index on a date field?

I have the following table definition: CREATE TABLE `bets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_mp` bigint(20) NOT NULL DEFAULT '0', `coin` char(3) COLLATE utf8_unicode_ci NOT NULL, ...
1
vote
1answer
85 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | +---...
1
vote
1answer
210 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
1
vote
1answer
51 views

One Non-Clustered Index that covers every possible combination of predicates?

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates. So, for example, if we have a table with columns A, B, C, etc we ...
1
vote
1answer
78 views

Full text index does not honour deleted words - They are still indexed

I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but ...
1
vote
1answer
147 views

DB2 Indexes are taking very large size on disk space

I have huge table in DB with estimated size 7 GB (I saw that using DB2CC and did the action Estimate Size for that Table). In estimate size dialog I noticed that two of my indexes for that table ...
1
vote
1answer
202 views

PostgreSQL isn't using indices in FULL OUTER JOIN

I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I ...
1
vote
1answer
47 views

PostgreSQL : Where is index kept until check point occures ?

In PostgreSQL data is first written to WAL files. When commit is done, data is written to WAL files and then return to client (synchronous commit). When checkpoint reached or WAL buffer becomes full, ...
1
vote
2answers
260 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...
1
vote
1answer
1k views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
1
vote
1answer
301 views

non clustered index on same table but the order is different?

I have a table with 5 columns col1...col5 . I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It ...
1
vote
1answer
599 views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
1
vote
2answers
231 views

Optimizing for data that does not fit in memory

We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from ...
1
vote
1answer
57 views

Choice of clustered key when almost all columns would be part of a unique index anyway

I am creating the following table CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS ( KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(), ...
1
vote
2answers
3k views

Compare Indexes/Constraints between two SQL Server databases

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. ...
1
vote
1answer
101 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, points....
1
vote
1answer
44 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
1
vote
1answer
38 views

Product table with possible duplicate part numbers from different manufacturers

I am making a database. It has a product(aka component or part) table with a ID, ManufacturerID, and PartNumber. The ID is obviously the primary key and is unique. The ManufacturerID is pulled from ...
1
vote
1answer
27 views

is there any need to index b again as it is already a under primary

Is there any need to index b again as it is already a under primary- if primary(a,b) and in query we have where b=' '; Is there any need to index(b) again individually because while indexing it ...
1
vote
1answer
89 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
1
vote
2answers
129 views

how to use an index on a mysql query with a where clause and a order by

I have a table CREATE TABLE activity ( id INT(10) UNSIGNED AUTO_INCREMENT, subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', action VARCHAR(250) NOT NULL DEFAULT '', ip INT(10) UNSIGNED NOT ...
1
vote
1answer
34 views

Index table for horizontal shard

Lets assume we have a vary amount of clients, which could manage some stuff on our site. Each client has his own table, which is used to store data. For instance: client_1, client_2, ..., client_N. ...
1
vote
1answer
216 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
1
vote
1answer
941 views

Error with Solr DataImporter using Mongodb

I've configured my environment and I've downloading Solr 4.7 The idea is make an indexing my mongodb database. For this I've installed the mongo-connector like appear here: https://github.com/10gen-...
1
vote
1answer
123 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM "...
1
vote
1answer
334 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
1
vote
2answers
174 views

index for OR operator with different columns

What would be best index for this kind of query: -- id is the PK create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null); select id from test3 where id=:1 and (name=:...
1
vote
1answer
302 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
1
vote
1answer
246 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
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" = ...
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
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 ...
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 ...
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 ...
1
vote
1answer
60 views

Creating an index on table A but with reference to table B

Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever ...
1
vote
1answer
189 views

Benefit to keeping a record in the database, rather than deleting it, for performance issues?

So I have a client that I am building a Rails app for....I am using PostgreSQL. He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
1
vote
2answers
2k views

MySQL Cluster - Table is full [Error 1114] while still creating tables

I am trying to forward engineer a MySQL Workbench model with about 100 tables to a development single-node cluster and, while creating the 51th table, I end up getting a "1114 table is full" error, ...
1
vote
1answer
526 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
1
vote
3answers
330 views

Unused Indexes - Consider Primary Key Constraint Supporting Indexes?

I have given a task to find out the unused indexes in my production environment. I'm using a DMV to find the unused indexes should i include the Primary key Clustered Indexes on it? If a Primary key ...
1
vote
1answer
336 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
1
vote
1answer
3k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
1
vote
1answer
141 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
1
vote
1answer
2k views

Why PostreSQL 9.2 does not use index scan if it can?

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan. Here is my table. The index I am talking about is name_left_prefix cwu=# \d web_city; ...
1
vote
1answer
430 views

SQL Index order and performance based on cardinality and data

I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting ...
1
vote
4answers
584 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; +------------------...
1
vote
1answer
345 views

I would like to query a range of criteria on multiple columns in MySQL

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example : We have 3 columns as "...
1
vote
1answer
433 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?