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)

2
votes
1answer
41 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
234 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 ...
1
vote
0answers
28 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
53 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
155 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
29 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 ...
1
vote
0answers
53 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
41 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
71 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
79 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
26 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
68 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
49 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
106 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
174 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
47 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
33 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
35 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
54 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, ...
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
31 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
32 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
58 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
54 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
27 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
37 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
70 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
31 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
44 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
32 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 ...
1
vote
3answers
78 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
2
votes
2answers
89 views

How do I get the DDL used to create function based index?

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it. DROP TABLE Fbi_tab; CREATE TABLE Fbi_tab ( a INTEGER, b INTEGER, c ...
1
vote
2answers
56 views

What is the ideal composite index for this query?

What is the ideal Composite Index in MySQL for the following query on an InnoDB Table1? SELECT ColA FROM Table1 WHERE ColB = 0 AND ColC = 0 AND ColD = 0 ColA is the left-most Primary Key. ColB ...
1
vote
1answer
40 views

PostgreSQL not using partial index when using boolean in WHERE clause

I'm trying to create a partial index which includes rows based on a boolean value but in my tests neither rows with the value set to TRUE or FALSE are included in the index, or at least the index ...
2
votes
1answer
52 views

How to start indexing a new data warehouse

I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds. I plan to use an index tuning tool ...
11
votes
1answer
313 views

Execution plan with multiple missing indexes

If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the ...
0
votes
1answer
60 views

Preventing MySQL index corruption

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen ...
3
votes
1answer
74 views

Index fragmentation increased significantly after rebuild

I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter. Mine might be such a scenario. I have an index on a ...
3
votes
1answer
122 views

Speeding up a GROUP BY, HAVING COUNT query

I'm trying to speed up this query in Postgres 9.4: SELECT "groupingsFrameHash", COUNT(*) AS nb FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876" GROUP BY "groupingsFrameHash" HAVING COUNT(*) ...
0
votes
0answers
33 views

Weird single-column “key” index in mysql

I am running MySQL 5.5.47-0+deb7u1-log (Debian). In a database I have two tables with exactly the same structure, roughly the same data (don't ask me why, its a long story..., and I know they are ...
1
vote
1answer
85 views

Postgresql 9.5 BRIN index dramatically slower than expected

I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was ...
0
votes
1answer
49 views

Query bytea column using prefix

Assuming I have a table with a bytea column called data on a table called t, how can I: Write a query that returns all rows which have a specific prefix on their data column. Index the query. I am ...
0
votes
1answer
61 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
7
votes
1answer
107 views

Index SEEK is not used unless OPTION (RECOMPILE)?

(Question moved from SO) I have a table (dummy data) with clustered index contains 2 columns : Now I run those two queries : declare @productid int =1 , @priceid int = 1 SELECT ...
1
vote
1answer
24 views

Postgres odd behavior with indices

I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The ...