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
85 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
50 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. ...
7
votes
3answers
265 views

Execution Plan is NOT using INDEX, It 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
2answers
87 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 ...
0
votes
1answer
64 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 ...
2
votes
0answers
34 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 [dbo]...
1
vote
1answer
57 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 ...
2
votes
1answer
32 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 ...
3
votes
1answer
62 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, ...
1
vote
1answer
115 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 ...
4
votes
5answers
154 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 ...
0
votes
0answers
60 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
0answers
33 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 ...
1
vote
1answer
65 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: http://...
2
votes
2answers
69 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 ...
2
votes
1answer
50 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 ...
5
votes
3answers
240 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 insert/update/...
1
vote
0answers
34 views

Mongo indexes question

I have the following aggregation query: db.getCollection('usersongs').aggregate([ {$match: { room: 'someObjId', status: { $in: [ "ended", "skipped" ] } } }, {$group: { ...
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 ...
3
votes
1answer
56 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 ...
2
votes
5answers
190 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 ...
0
votes
1answer
32 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
41 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 ...
1
vote
0answers
60 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

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 ...
1
vote
1answer
76 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 ...
3
votes
1answer
82 views

Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to ...
1
vote
2answers
27 views

Does MySQL updated index if index key is not updated?

Say I have a table with 3 columns: ID (PK), Name, Age. Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update ...
1
vote
1answer
75 views

SQL Server : ETL Stage Index

We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the ...
0
votes
0answers
62 views

When to reindex postgres index?

I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed. When should I reindex ...
1
vote
3answers
135 views

Mysql - How number of columns effect the database performance

I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal ...
5
votes
1answer
187 views

How to index for inequality queries?

I have query that is excluding data based on the value of a float column select * from My_Table where my_Float_column != 0 and my_Float_column is not null I don't want to index a float type if I ...
0
votes
0answers
97 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
3
votes
1answer
54 views

Does updating a jsonb column trigger updates on all expression indexes on that column?

I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column): UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123 ...
1
vote
1answer
38 views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
2
votes
2answers
56 views

Index for OR operator: a=x or b=x

I have table with three integer columns id, a and b. I want to get all records where a or b match specified parameter ordered by id: select id, a, b from t where a=x or b=x order by id Note that x ...
2
votes
2answers
56 views

Is there much difference between having a key (intA, intB), or having key (longAB) where longAB is the concatenation of bytes of intA and intB?

This is a bit of an odd question. Let's go with MySQL for example. I don't know a ton about db internals. Compare two scenarios, in one, we have a table with a composite key of the from (intA, intB)...
2
votes
1answer
26 views

How do I find what keys mysql will use for a query?

I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and ...
1
vote
1answer
33 views

Is it possible to have unique + null

I have a need to have a database table that has a column that is either null, or unique values. I know I can control that in code, but is there a way to do that with an index as a backup? Or is that ...
0
votes
1answer
42 views

Which columns to index with LEFT JOIN and GROUP_CONCAT

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching: SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, ...
2
votes
0answers
43 views

Postgres planner overerstimate index only scan

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

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
0
votes
0answers
56 views

Why my MySQL (myisam) index is broken and how to avoid it?

I have big composite (multiple-column) index on MySQL MyISAM table. And I noticed it doesn't work. I ran SHOW INDEX command. There are NULL in the Cardinality column on the most index fields (except ...
0
votes
1answer
47 views

MYSQL ORDER BY Performance

We have a query like this: SELECT PROJECT.PROJECTID, REQUEST.REQUESTID, SAMPLE.SAMPLEID,SAMPLE.OTHERSAMPLEID, ILLUMINASEQPROTOCOL2.SAMPLEID FROM ILLUMINASEQPROTOCOL2, PROJECT, SAMPLE, ...
0
votes
0answers
31 views

N-dimensional vectors in PostgreSQL Spatial Index

I have many 8-dimensional vectors. And I want to construct on them a R-tree ('USING gist' in PostgreSQL). But I have got stucked at the choice of the correct geometrical type. Whether the 'path' type ...
0
votes
0answers
67 views

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished? I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
2
votes
1answer
85 views

Index columns with arithmetic operators

I commonly make this query on my database SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;. Is it possible to create indexes based on Column_a + Column_b < 10 to speed up this lookup. ...
1
vote
1answer
32 views

InnoDB slow query (few derived and dependent queries)

I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins). I need to get all 0 values from those tables in the last WHERE ...
0
votes
2answers
93 views

What is the minimum privilege required to create a full text index?

I just created a full text catalog on my database. I now have a developer who needs the ability to create full text indexes on the tables in this database. The suggestion given in a previous ...
0
votes
1answer
35 views

Query not using indexes

I have a query which is not using the timestamp index and scanning 200 million records. I know that the query will not use the index if there is a function applied on top of it. However is there any ...