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)

5
votes
1answer
168 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, non-...
2
votes
0answers
96 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
1
vote
2answers
2k views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
0
votes
2answers
52 views

How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

I want to rebuild my indexes and statistics on DB. How can I select this option on production maintenance. SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service ...
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 ...
0
votes
1answer
29 views

What can cause an Index “total writer” to be the same in every index?

I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)). I use this query to analyze unused Indexes: --Possible bad Indexes (writes > reads) SELECT ...
0
votes
1answer
190 views

Recommended number of index for a table

I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows. This table is very requested and all these column too. I use to run the following ...
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
2answers
229 views

Is it a good idea to include a value column (e.g. from measurements) in an index?

Is it a good idea to include a value column into an index for a table which has many million rows? For example a measurement table which looks kind of like this: CREATE TABLE measurements ( id ...
0
votes
1answer
166 views

Oracle can't drop index with . in name?

I am trying to drop an index that has a dot(.) in its name and can't seem to do it (I'm logged in as the SYSTEM user, which owns the index). Any ideas? SQL> select index_name,owner,status from ...
2
votes
0answers
229 views

Finding missing_index_details in MySQL

MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL? This article (scroll to "Find missing indexes") says that in Microsoft SQL ...
2
votes
1answer
213 views

Automatic deduplication (normalization) of strings in MySQL

The question is whether MySQL can use separate physical storage for strings transparently, but with the same operations that are applicable to varchar, including %. I have a large number of ...
5
votes
1answer
337 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? Motivation:...
0
votes
2answers
234 views

How to tell MySQL to ignore all indexes for a given query?

I've been benchmarking several queries and learning about indexes by using the explain function, profiling and the SQL_NO_CACHE option. My usual benchmarking session goes something like this: SET ...
3
votes
3answers
292 views

How does like query work with indexed table

I am working in Spring-java and mysql. I have to query the table of size 100k records. Table has say 10 columns. And In my sql select query I have to make like queries with %text% search on say 4 ...
0
votes
0answers
76 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 ...
2
votes
1answer
773 views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
1
vote
1answer
1k views

Recommended Postgres settings to speed up indexing on a 100GB database?

I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk. I will be using this server to serve both my site and my database (i.e. it's not just a database server). ...
1
vote
1answer
404 views

How to access the text definition of sql indexes

I am able to access the text definition of stored procedures, views, triggers and functions from sys.sql_modules. I want to get the text definition of indexes. How can I get the text definition of ...
0
votes
1answer
73 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
2
votes
2answers
158 views

Is it a good idea to create an index for each column in a new table? [closed]

I will short my question. (SQL-Server 2008) Is it a good idea , when crating a new table, to create an index for each column? Why i'm saying this? Today I was creating a database, and my boss was ...
1
vote
1answer
217 views

What is an index KEY - emphasis on KEY?

In SQL Server, What does the 'key' part of the phrase 'index key' mean? Is the key a number? what is it? If I have the table of data containing a column called LastNames, and I have, say, a clustered ...
1
vote
0answers
484 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 ...
2
votes
1answer
76 views

How should a numeric value that is an external key be stored?

This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows ...
1
vote
1answer
42 views

Performance of Combination lookup

I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here. Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have ...
0
votes
1answer
45 views

Index for a Postgres query with a sort and an equality

This query is very slow: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Follows" AS "Follow" INNER JOIN "Users" AS "followee" ON "Follow"."followeeId" = "followee"."id" WHERE "Follow"."followerId" ...
1
vote
2answers
150 views

What to do with a table with too many indexes?

I'm the new DBA for this company. I'm seeing some of their tables have a LOT of indexes; for instance some have over 50 or 60. Is this a good thing? From my research, it seems this is not good for ...
0
votes
1answer
38 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
252 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 ...
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 ...
2
votes
2answers
316 views

Index for numeric field is not used

I have PG server (9.0.4) installed on Debian x86. In one of my tables I use numeric(6,1): CREATE TABLE mytable(name text, numfield numeric(6,1)) Also index created for this field: CREATE INDEX ...
1
vote
2answers
1k views

Postgres Index a query with MAX and groupBy

Is there any way to index the following query? SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id; I've tried creating sorted (non-composite) indexes on frame and time,...
0
votes
0answers
163 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
1answer
39 views

Sorting on compound Indexes

This may be a novice question but could it be possible query here. If I have a compound Index as { a: 1, b: 1, c: 1, d: 1 } Is this query possible? db.data.find( { a: {$gte:1,$lt:2}, b: { $gt: 3} ...
1
vote
4answers
1k views

with MySQL, are multiple single column indexes faster than one multi-column index? [closed]

I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?
0
votes
1answer
107 views

Delete index while query is running, is it possible?

I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still ...
0
votes
1answer
406 views

Optimizing query for large number of rows in mysql

I have the following database table in MySQL : and is running the following query SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, `...
1
vote
1answer
146 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 ...
0
votes
0answers
43 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 ...
2
votes
0answers
66 views

SQL Server 2008 R2 - Should I be worried at this statistics regarding indices causing locks?

We have some mild table locking issue during our peak hours so I figured the following query query taken from this site would be a good start. And boy, does it show me some seriously concerning ...
1
vote
1answer
200 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
134 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
2
votes
1answer
100 views

Performance of bitwise operators on byte column vs individual bit columns

I'm designing a database schema at the moment, but have a performance query. Our table needs to have (at least) two flags, or boolean values. We can call these IsComplete and IsExportable. The ...
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, ...
0
votes
1answer
1k views

No syntax for Nonclustered index in MySQL?

So, I have seen people discussing about differences between clustered V Non Clustered index, however, I didn't see any syntax for Non Clustered Index in MysQL. How does MySQL creates Nonclustered ...
6
votes
3answers
296 views

REORGANIZE seems to work as well as REBUILD all the time

On this MSDN Page it says if you should reorganize or rebuild based on the amount of fragmentation: 5% to 30% -> ALTER INDEX REORGANIZE Over 30% -> ALTER INDEX REBUILD WITH (ONLINE = ON)*...
2
votes
2answers
88 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND "User"."...
3
votes
1answer
110 views

What is “cover-to-cover indexing”?

I came across this term reading my lecture notes; I can't find it on the internet. My notes say the following: Given a schema R(A,B) and a query of the form "A=a", or "B=b", or "(A=a AND B=b)". ...
3
votes
2answers
2k views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
3
votes
1answer
352 views

Is a clustered index on a child table in a parent/child relationship the most optimal index?

A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a ...