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)

7
votes
1answer
4k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as ...
1
vote
1answer
193 views

Is it worth indexing a varchar(32) column of UUIDS where half the UUIDS are v1 and half are v4?

I have a MySQL InnoDB table with tens of millions of rows. Each row has uuid column. They are stored in the standard VARBINARY(16). I was recently asked to add a column to this table of VARCHAR(32) ...
6
votes
2answers
1k views

Can rebuilding indexes cause worse performance after the rebuild is finished?

We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is ...
1
vote
1answer
565 views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...
0
votes
1answer
82 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
2answers
914 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 ...
5
votes
2answers
105 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] ...
2
votes
1answer
109 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
69 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
653 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
123 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
109 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
64 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
vote
1answer
382 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 ...
3
votes
2answers
1k 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 ...
1
vote
1answer
176 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
459 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 ...
4
votes
2answers
1k 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
218 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
750 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 ...
4
votes
2answers
252 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
339 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+ ...
2
votes
0answers
477 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
37 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 ...
5
votes
1answer
845 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
104 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
147 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 ...
4
votes
1answer
368 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
259 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. ...
2
votes
1answer
342 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
864 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
448 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
1answer
227 views

Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index. When there is not a clustered index (Heap) ...
0
votes
1answer
115 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
187 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
129 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
100 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
51 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) ...
2
votes
1answer
214 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 ...
2
votes
1answer
448 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 ...
1
vote
1answer
259 views

non clustered index on same table but the order is different?

I have a table with 5 columns col1...col5 . I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It ...
0
votes
1answer
76 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
69 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
130 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
1answer
135 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
60 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
108 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
269 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
499 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
107 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 ...