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)

3
votes
0answers
40 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, ...
2
votes
0answers
41 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
35 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
1answer
31 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
23 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
15 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
58 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 ...
0
votes
1answer
13 views

MySQL not using optimum composite key [on hold]

I have a table which I've added a couple of composite keys to: CREATE INDEX idx_foo on my_table(col_one, col_two, col_three); CREATE INDEX idx_bar on my_table(col_one, col_four, col_three); Why, ...
0
votes
0answers
27 views

When I can DROP an index safely? [duplicate]

I would like to get rid of unused indexes in my database. I have identified many indexes that are never used (seeks, scans and lookups are 0), and are being updated during DML operations. I used ...
0
votes
1answer
36 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
182 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
28 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 ...
1
vote
0answers
19 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 ...
1
vote
1answer
25 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 ...
4
votes
1answer
45 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? ...
0
votes
2answers
20 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 ...
-4
votes
0answers
24 views

Microsoft SQL Server database user permission. that user can not create database [closed]

Grant a user permission that can not create databases. Instead work every thing, like see table work in on it run query. What is meant here is that I wish to create a user who has SELECT privileges ...
3
votes
3answers
51 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
38 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 ...
0
votes
0answers
61 views

Where are indexes stored in memory and how do they improve performance? [closed]

Where are indexes stored in memory? I am studying indexes in SQL and have not found an exact answer. Can anyone please explain the process? Also, if a table has empid as an indexed column and I want ...
1
vote
1answer
44 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
48 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). ...
0
votes
1answer
28 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
25 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
74 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
81 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 ...
0
votes
0answers
50 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
30 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
39 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 ...
-1
votes
0answers
20 views

Optimize a query sorted by a join value [duplicate]

This query fetches the likes on a post from people you follow, sorted by the number of followers they have: EXPLAIN (ANALYZE, buffers) SELECT * FROM "Likes" AS "Like" INNER JOIN "Users" AS "user" ...
0
votes
1answer
28 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" ...
0
votes
2answers
68 views

What to do with a table with a lot ( 50+ ) indexes? SQL Server 2008

We have a table here, used by our software. Since i'm new in this company ( the new dba here ), what i see is that some tables of some databases have a LOT of indexes ( like 50...60 ). Is this good? ...
0
votes
0answers
24 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
9 views

Granting permission on an altered table in Teradata

I have a table, which has two columns say A and B as part of the Primary index PI_AB. I have to alter the length of B column - increase it. I was planning to alter the primary index to just have A, ...
0
votes
0answers
39 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
0answers
34 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
2answers
45 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
59 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 ...
0
votes
0answers
45 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
28 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
63 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
39 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
24 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
28 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
34 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
36 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
54 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
70 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
0answers
26 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
23 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, ...