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
0answers
24 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
68 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
68 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
41 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
83 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
49 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 ...
1
vote
1answer
129 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
130 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
41 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
34 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
90 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
103 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
188 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
41 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 ...
0
votes
1answer
33 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
82 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
1answer
30 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
30 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
85 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
56 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
82 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
138 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
99 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
34 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
151 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
50 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
98 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
71 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
42 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
43 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
75 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
82 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
32 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
28 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
280 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
236 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 = ...
0
votes
0answers
43 views

Mongo find query is not using indexes in java code but working on mongo cosole

I found a strange issue. When i run mongo query from my java code with explain true it does not show me index used in the query but when i run it using mongo console it works fine . ...
2
votes
2answers
77 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 ...
3
votes
1answer
50 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)". ...
1
vote
2answers
138 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
172 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 ...
1
vote
1answer
60 views

Does the length of the index name have any performance impact?

This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could ...
2
votes
0answers
43 views

Filtered Index is not selected once an identical, non-filtered, index is made [duplicate]

I have a lot of historical data, but 98% of the time, my data entry team only looks up data from the last 5 days. So I added a filtered index to help those lookups be faster. My test query looks ...
1
vote
2answers
111 views

Are nonclustered indexes used when joining tables?

I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them. But I've read recommendations to noncluster index all FKs, I ...
0
votes
0answers
34 views

Very small indexes

What is the recommended best practice in dealing with indexes that store very little amount of data (like a few or few hundred pages)? Should these indexes be even maintained? Shouldn't they be ...
1
vote
2answers
124 views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's ...
2
votes
1answer
185 views

SQL Server : Index Seek with very high Scan Count [closed]

I have a query that joins 2 tables: Documents table, that has DocID as Unique Clustered Index DocumentsRows table, that has DocID, RowID as Unique Clustered Index When I join those 2 tables, I ...
6
votes
0answers
211 views

Timestamp-based index

I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered ...
1
vote
2answers
516 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...