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)

6
votes
1answer
359 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
2answers
3k views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
6
votes
1answer
433 views

Permission to create Spatial Index

Permissions required to create a Spatial Index on a table, for this BOL says: Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the ...
6
votes
1answer
414 views

Can spatial index be a part of a multiple columns index?

This manual over here is extremely unclear and doesn't even provide some sample SQL statements: http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html Another way to rephrase the question is the ...
6
votes
1answer
2k views

Reindex Task Failing Randomly [closed]

We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
5
votes
2answers
181 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
5
votes
3answers
107 views

Interpreting an execution plan

I need some help analysing some simple t-sql execution plans. I have a table [User] with the following 3 columns: Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50)) I have Created an ...
5
votes
1answer
598 views

Don't rebuild indexes < 1000 pages - then why bother having them?

Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM). If I'm not rebuilding them, ...
5
votes
3answers
134 views

Dynamic temporary index creation

In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that ...
5
votes
3answers
423 views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
5
votes
2answers
7k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
5
votes
4answers
6k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
5
votes
2answers
2k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
5
votes
1answer
799 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
5
votes
1answer
3k views

Move Primary Key to Filegroup (SQL Server 2012)

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient: Drop non-clustered indexed (requires them to be resorted and ...
5
votes
1answer
588 views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
5
votes
2answers
506 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
5
votes
2answers
144 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ...
5
votes
3answers
259 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
5
votes
1answer
195 views

Composite or Single-Field Clustering Key

My DBA and I are having a disagreement on index structure. Consider data for a medical claim... We have the Header table with fields like: ClaimId (varchar(50)), PaidAmount, MemberID... and for ...
5
votes
1answer
690 views

What is a non-clustered index scan

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered ...
5
votes
1answer
161 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
5
votes
2answers
887 views

Should I rely on Database Engine Tuning Advisor for creating indexes?

How should I rely on Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index ...
5
votes
1answer
2k views

How are multiple indexes used in a query by MySQL?

Imagine a table where there are separate indexes on columns a and b. The query runs somewhat like this: SELECT col1, col2 FROM `table` WHERE a = 'value_1 AND b = 'value_2'; Now, does this query ...
5
votes
1answer
640 views

Index defrag takes forever to run

We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last ...
5
votes
3answers
397 views

SELECT * WHERE VarCharColumn IN (…) Optimization

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution. The column is ...
5
votes
2answers
164 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
5
votes
1answer
224 views

How to disable or delete an index

I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again. Unfortunately ...
5
votes
2answers
148 views

Why is an entire index that is infrequently used stored in the buffer pool?

I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire ...
5
votes
1answer
4k views

Improve speed of index rebuild on SQL server

I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import. Now I want to ...
5
votes
3answers
1k views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
5
votes
1answer
795 views

How to Index a JOIN result of two or more tables in order to improve the performance in SQL server?

I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating ...
5
votes
4answers
5k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
5
votes
2answers
161 views

“Recheck Cond:” line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
5
votes
3answers
276 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
5
votes
1answer
2k 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 ...
5
votes
2answers
3k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
5
votes
2answers
1k views

Pagination with PostgreSQL 9.3: counting number of pages

I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id. -- index create index category_score_id on products(category, score, ...
5
votes
1answer
111 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
5
votes
1answer
521 views

SQL Server: Effect of Setting These Index Options

A reindexing script was recently run in our dev environment that set ALLOW_ROW_LOCK = FALSE and ALLOW_PAGE_LOCKS = FALSE on all the indexes in the database. This was done while testing new reindexing ...
5
votes
2answers
2k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
5
votes
2answers
567 views

Optimizing IP Range Search?

I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query. In my table I have 3 columns CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
5
votes
1answer
537 views

Will a clustered index improve performance of updates performed sequentially on that clustered index?

I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in ...
5
votes
1answer
1k views

Estimating impact of indexes SQLite database size

I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to ...
5
votes
2answers
592 views

Any advantage of having a second index with included columns on a primary key?

I'm reviewing our database and noticed that on a particular table we have the following indexes: Table: Col1 INT IDENTITY(1,1) Primary Key Col2 INT ... about 15 more columns .... ColN ...
5
votes
2answers
647 views

Should I use a composite or single-column index?

I have the following columns in my database table (Medicines). ID bigint, MedicineName nvarchar(50), BrandName nvarchar(50), MedicineCode nvarchar(20), and price,quantity. I am making a stored ...
5
votes
1answer
6k views

Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?

I have an existing table: CREATE TABLE dbo.ProofDetails ( ProofDetailsID int NOT NULL CONSTRAINT PK_ProofDetails PRIMARY KEY CLUSTERED IDENTITY(1,1), ProofID int NULL, ...
5
votes
1answer
330 views

RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise

Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits? The last two weeks one of our applications has had downtime due to ...
5
votes
2answers
116 views

Algorithmic order of table indexes for table operations

I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations. I am not interested in the nuts & bolts of specific implementations; ...
5
votes
1answer
1k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...