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)

0
votes
0answers
23 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
0
votes
1answer
26 views

Why Does the Update Statement also updates indexes?

I am currently learning about SQL queries and how indexes work with them, and I ran into a situation that I don't understand. Let's say I have this query for that updates a tables' values with another ...
3
votes
3answers
273 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
3
votes
2answers
64 views

New Index on table cause table to be locked really often

Here are my tables : CREATE TABLE [dbo].[Trackers] ( [IdTracker] INT IDENTITY (1, 1) NOT NULL, [IMEI] NVARCHAR (16) NULL, [CreationDate] ...
0
votes
0answers
13 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
1
vote
1answer
62 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
2
votes
1answer
38 views

Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column. While trying to optimize some queries, I created a new partial index on the same column. Using ...
0
votes
1answer
31 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
0
votes
0answers
32 views

Unique Constraint exception after dropping constraint

We had dropped a unique constraint using the ALTER TABLE DROP CONSTRAINT script, but it is still throwing the Unique Constraint exception while trying to insert. We drilled this down to two possible ...
1
vote
1answer
66 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
1answer
40 views

Which Indexes in which situations is more efficient in SQL Server 2012

I use SQL Server 2012. I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... ...
-1
votes
0answers
30 views

How index work in case of write operation?

We have two cases:- Insertion :- Cluster Index:-- Insertion :- we have number of user which we doing insertion new records. For .........example 27 users had inserted 27 rows in 1 ...
0
votes
1answer
12 views

Disable TTL thread in MongoDB

Is there any way to disable TTL deletion thread in mongoDB? I know that you can modify the ttl index expireAfterSeconds value to a high number in order to "technically" stop ttl from deleting, but i ...
2
votes
2answers
62 views

space usage on sys.allocation_units and sp_spaceused

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a ...
0
votes
0answers
22 views

Mongodb compound index on two fields where one field is sparse

I have a collection of documents that give stats relating to videos. I have a unique compound index on videoId, date (there is at most one document per video per day). I want to be able to; in a ...
1
vote
1answer
77 views

Rebuild index in table with many values

I'm junior DBA ,have table with 11 million values ,one of the indexes is like : CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] ( [my_id] DESC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
73 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
3
votes
2answers
92 views

SQL Server 2008R2 XML Column Index strategy for large tables?

I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
1
vote
1answer
54 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
3
votes
1answer
53 views

PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key

If I have a table containing: CREATE TABLE test( id SERIAL PRIMARY KEY, name VARCHAR(200), age INT, data JSONB ); and data column populated with {"name": xxx, "age": yyy}, sometimes ...
2
votes
2answers
129 views

SQL Server doesn't use expected index

I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL): SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP ...
3
votes
1answer
54 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
1
vote
0answers
35 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
0
votes
1answer
26 views

optimize my query mysql

I have the following query: SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName ...
1
vote
1answer
58 views

Check if an index exists by comparing columns

We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order. ...
2
votes
1answer
25 views

How to get the spatial index in CREATE TO script?

Is it possible to get the spatial index using the create to script using SQL Server 2012? When I write out a table using SCRIPT TABLE AS > CREATE TO in Microsoft SQL Server Management Studio 2012, I ...
3
votes
1answer
62 views

PostgreSQL CREATE INDEX memory requirement

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table. The table and the resulting index together (or twice ...
2
votes
1answer
57 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
0
votes
1answer
78 views

Increase performance WITHOUT adding indexes [closed]

I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't. ...
1
vote
1answer
38 views

Does a database update force index rebuild regardless of updated field?

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated. For example, if I have a table with 20 ...
1
vote
1answer
43 views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
2
votes
1answer
31 views

MySQL table indexes change location [closed]

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
3
votes
2answers
124 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
0
votes
1answer
40 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...
1
vote
0answers
73 views

Duplicate Key Entry [closed]

I've got a problem on SQL Server 2008 where I'm getting a duplicate key entry error on a process that's worked fine in the past. What is really weird is that if I drop the clustered index, insert the ...
0
votes
1answer
34 views

Does remove document automatically remove the index that reference this document?

I am wondering if removing the document will automatically update the index if this document is referenced?
1
vote
2answers
88 views

What Columns to create Indexes on?

I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate. These 3 columns are NOT unique. The table is a ...
0
votes
0answers
31 views

How best to index large table with many index keys

In our datawarehouse load, we bring data in the following manner. 1. from source to stage 2. stage to a consolidation layer 3. from consolidation layer to operational data store (ODS) ...
0
votes
1answer
73 views

Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns ...
1
vote
1answer
45 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
0
votes
1answer
31 views

MySQL uses intersection instead compound index

I have compound index on following columns: (country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count) When I run following query: SELECT COUNT(*) FROM `user_profile` WHERE ...
0
votes
1answer
9 views

Record in table won't show in form

I have an Access 2010 database that has "frmCatalog" which is sourced from the Catalog table. The Catalog table shows a record with the primary key "Acq" = "5203." But the form does not show 5203. ...
0
votes
2answers
58 views

Solution on a complex sub select query SQL Server

I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this: Select *, (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA, ...
3
votes
2answers
97 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
1
vote
1answer
41 views

What happens if an index build is cut half way through?

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 ...
1
vote
0answers
36 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
2
votes
2answers
84 views

What is difference between is_unique column and is_unique_constraint columns in sys.indexes

We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns Create statement for both types of ...
1
vote
1answer
69 views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
1
vote
0answers
28 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
0
votes
0answers
25 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...