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
219 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 ...
1
vote
1answer
964 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
-1
votes
0answers
15 views

How external/internal fragmentation effect to query performance [duplicate]

could you explain me what is external fragmentation and internal fragmentation? what does that effect to query performance Thanks in advance
5
votes
1answer
73 views

Conditionally add an index to a temp table

I have a stored procedure that looks something like this: SELECT columnlist INTO #temptable FROM Table JOIN lotsofothertables WHERE severalconditions UPDATE anothertable SET column = ...
0
votes
2answers
198 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
-1
votes
0answers
16 views

PostgreSQL Index [on hold]

Im using PostgreSQL and have a question about creating an index. I know that I have approximately 1 million entries it a table called 'item', 400,000 items in a table called 'recordings' and 100,000 ...
1
vote
1answer
26 views

InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant ...
3
votes
1answer
43 views

B Tree and B+Tree differences

I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate: Why can I store more search keys on an B+ Tree Index? My guess ...
6
votes
3answers
213 views

Execution Plan is NOT using INDEX and uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
0
votes
0answers
59 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
1
vote
1answer
126 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
2
votes
1answer
18 views

Why does Cassandra recommend against creating an index on high-cardinality columns?

The Cassandra documentation states, Do not use an index in these situations: On high-cardinality columns because you then query a huge volume of records for a small number of results. See ...
-1
votes
1answer
44 views

Why and how to index a MySQL database [on hold]

How to do indexing and what is the use? Please explain in such way so I can use it practically.
26
votes
3answers
30k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
1
vote
1answer
43 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
1
vote
1answer
33 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
4
votes
5answers
283 views

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented ...
0
votes
2answers
80 views

Clustered Index Misunderstanding

I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record. Some say, clustered index stores the whole records in ...
1
vote
1answer
117 views

How can I rebuild an index on table with 100 million rows without a wait time out?

I have the following table with ~100 million rows: CREATE TABLE Result ( MetricId int NOT NULL, PeriodId int NOT NULL, Value decimal (18, 2) NOT NULL, CONSTRAINT PK_Result PRIMARY KEY ...
0
votes
0answers
23 views

Creating clustered primary key index with ONLINE option

I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to ...
1
vote
1answer
68 views

Materialized Subquery Not Optimized

I am having trouble optimizing a materialized subquery in MariaDb 10.0.15 and having difficultly understanding the documentation on whether I can even optimize this query any further. This query ...
1
vote
1answer
56 views

Complex key performance problem (MySQL, InnoDB)

A query is painstakingly slow in both production environment and (fortunately reproducable) on my local dev box. Production is Linux and my dev is currently Windows 10 with MySQL 5.7.11: SHOW ...
3
votes
1answer
51 views

non-optimal use of index

I'm somewhat puzzled by the scenario below. The table involved (relevant parts) is defined as: CREATE TABLE Nya. UPSEC_COURSE ( UPSEC_COURSE_ID CHAR(11) NOT NULL, ...
4
votes
5answers
134 views

Is there any best number for rows count in a table to start using indexing?

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between ...
10
votes
2answers
399 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 ...
0
votes
1answer
9 views

Postgresql error: failed to re-find parent key in index

I haven't seen this before and am stumped. I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently I VM ran out of disk space (resulting in no space left on device ...
1
vote
0answers
29 views

Index on a highly updated field causing excessive locks in SQL server

I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with. CREATE TABLE ...
7
votes
1answer
194 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 ...
2
votes
1answer
19 views

In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory ...
2
votes
2answers
58 views

Filtered index error when using two column condition

I created a filtered index (in SQL Server 2012) and compared it with non-filtered index – I could see promising improvement as shown below. Now I need to change the filter condition to use two ...
1
vote
1answer
99 views

Which field should be the candidate for index in this case?

You're tracking visits already in another table that contains quite some information about the visit. In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use ...
1
vote
0answers
27 views

What is the maximum value for the index_length of a MySQL 5.5 table?

I'm trying to find out why a series of inserts is painfully slow (1 minute 15 seconds, on average) on a dedicated MySQL 5.5.47 server, while the same inserts are normally fast (less than 5 seconds) on ...
0
votes
1answer
129 views

Does remove document automatically remove the index that reference this document?

I am wondering if removing the document will automatically update the index if this document is referenced?
0
votes
1answer
32 views

How to use sequential UUID primary key for range query by date

I'm using a sequential timebased GUID as my primary key in MySQL 5.6 in a transaction table with over 1 billion rows. The application is written in JAVA and GUID is generated as described here: ...
5
votes
1answer
823 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 - ...
1
vote
1answer
147 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
2
votes
1answer
37 views

SQL Server spatial geometry properties on index

Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012. I have mixed SRID table and I was wondering if it ...
0
votes
1answer
68 views

Index rebuild, data compression generated Large TLog

We are following vendor recommendations for a index rebuild changing the fillfactor and enabling data compression, on the test server the results have us wondering what is going on that would cause ...
2
votes
5answers
140 views

Lots of Indexes - MySQL vs MongoDB - Migrating

So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of ...
5
votes
3answers
226 views

Shrinking database which is not having any insert/update

We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any ...
8
votes
2answers
646 views

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless ...
3
votes
1answer
49 views

What indexes to use for GroupWise max

I have a user table and group table, and a many to many relationship between them. I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and ...
1
vote
0answers
25 views

Mongo indexes question

I have the following aggregation query: db.getCollection('usersongs').aggregate([ {$match: { room: 'someObjId', status: { $in: [ "ended", "skipped" ] } } }, {$group: { ...
2
votes
0answers
40 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
0
votes
0answers
29 views

Fast min / max values of column in big table with index [duplicate]

I have a huge table public.data_minutesample: Column | Type | Modifiers ----------+---------+----------- probe_id | integer | not null power | integer | not null minute | integer | not ...
1
vote
0answers
49 views

sys.dm_db_index_physical_stats slow on first run after rebuilding indexes

I have a 10GB database in SQL Server 2016 RC1 on Windows Server 2012 R2 where I just performed many insert and delete operations. Afterwards, I ran: EXEC sp_MSforeachtable @command1="SET ...
2
votes
0answers
42 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
0
votes
1answer
27 views

see currently running background indexing jobs (MongoDB)

In mongodb.log I see 2016-03-31T12:44:11.003+0200 I - [conn58191] Index Build (background): 14396400/17648149 81% 2016-03-31T12:44:14.018+0200 I - [conn58191] Index Build ...
0
votes
2answers
35 views

postgresql function index not being used by query

I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not ...
0
votes
1answer
121 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...