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)

4
votes
1answer
788 views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
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 ...
8
votes
2answers
8k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...
1
vote
2answers
391 views

Is Log Shipping or Mirroring a better solution for Disaster Recovery?

I am planning an index rebuild, however my logs are prone to huge overblown sizes. In fact I was leaning towards doing an intelligent re-indexing, but since the fragmentation levels are greater than ...
4
votes
2answers
1k views

Could running ANALYZE on all databases on large InnoDB tables show some performance increase?

So we have an old database that was fragmented until we ran OPTIMIZE on all tables. The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity. There are so many ...
2
votes
2answers
692 views

rebuilding indexes and logshipping?

We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth? Do we increase the frequency of the ...
2
votes
1answer
2k views

PAD_INDEX and FILLFACTOR on clustered Identity index

I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting ...
3
votes
2answers
440 views

MySQL InnoDB Index in swap

I can't find any info regarding my question. This is more theoretical question. For example, i have table keywords. CREATE TABLE IF NOT EXISTS `keywords` ( `kid` int(11) unsigned NOT NULL ...
2
votes
1answer
1k views

MySQL query not using an index when table contains many records

I have a table: CREATE TABLE `p` ( `id` BIGINT(20) unsigned NOT NULL, `rtime` DATETIME NOT NULL, `d` INT(10) NOT NULL, `n` INT(10) NOT NULL, PRIMARY KEY ...
1
vote
3answers
897 views

Improve performance of a Fact Table

I have a Fact table CardTransactionFact Table Structure TABLE [dbo].[CardTransactionFact] [CardTransactionID] [int] IDENTITY(1,1) NOT NULL, [TransactionTerminalID] [int] NOT NULL, ...
6
votes
2answers
5k views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
4
votes
1answer
361 views

Improve query performance with index

I am looking to create an index for a database to acheive better performance. I am using this query: SELECT DISTINCT t1.* FROM current_order AS t1 LEFT JOIN receipt AS t2 USING ...
3
votes
1answer
1k views

How do I reindex a MYSQL table using a different disk?

I have my mysql data and index files on disk1 I need to reindex the table and do NOT have enough disk space on disk1 How do I reindex a table on disk1 and use disk2 for the reindex disk space? For ...
6
votes
2answers
2k views

Rebuilding index has impact on mdf/ldf files SQL Server 2008

While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files. What does it exactly means? Is complete process done on TempDB and does ...
5
votes
1answer
408 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 ...
8
votes
2answers
195 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
5
votes
2answers
618 views

Partition Function vs. Clustered Index

We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
3
votes
1answer
7k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
2
votes
2answers
344 views

SQL Server and Over-Indexing Tables [duplicate]

Possible Duplicate: Where can I find some guidance on index strategies? I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many ...
5
votes
1answer
292 views

Query for data that is not there

I have this setup attendance table: ------------------------------------------ | member_id | attendance_date | event_id | ------------------------------------------ attendance indeces ...
0
votes
1answer
729 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
7
votes
2answers
1k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
3
votes
1answer
204 views

I have Fragmented Index on a empty table!

I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
3
votes
2answers
1k views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
6
votes
3answers
2k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
1
vote
1answer
1k views

Does MySQL have a limitation of one index per query?

MySQL supports spatial index. However, all the spatial index can do is find points in a box. It can't do fancy stuff like find the nearest 1000 points, etc. I think, and I don't know where I went ...
2
votes
3answers
640 views

SQL Server : explicitly create an index on a primary key and unique fields

UPDATED: I'll make the question clearer as the first answer isn't quite what I was looking for. How can I execute this create table statement and ensure that the two automatically created indexes ...
4
votes
4answers
625 views

Can I reorganise index on a sql server 2005 database without performance hit on production server

I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
6
votes
2answers
153 views

Unique Contraint/Index Based On Values

I have a table that has the following column definitions: ID (INT, PK) Name (VarChar) Active (Bit) Bunch_of (Other_columns) Question: I want to have a Constraint on Name/Active such that we can ...
1
vote
0answers
37 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS ...
1
vote
2answers
1k views

How can I optimize this mySQL table that will need to hold 5+ million rows?

The table has about 12 rows. Indexes: idint(11), Unique, auto-increment, primary ATTACHMENTIDvarchar(14), Unique MLSNUMBERbigint(20) POSITIONint(10) FILETYPEvarchar(32) I'm doing an extended ...
4
votes
1answer
468 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
5
votes
1answer
465 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 ...
2
votes
2answers
2k views

difference between having many non clustered index with single columns and with combination of many columns

Folks I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed many non clustered index with single columns and non ...
1
vote
1answer
307 views

Should indexes automatically be placed into their own bufferpool?

We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX. Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG ...
4
votes
2answers
631 views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); ...
2
votes
1answer
248 views

MySQL: Very slow query, not using index, no joins involved

I am running what I think is a very simple query: SELECT SQL_NO_CACHE m.mbr_nbr FROM tran.member m WHERE m.cardnbr = 000099999930 The table member is a poorly written one, with 133 columns and ...
2
votes
2answers
676 views

Reorganize Indexes T-SQL

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
1
vote
1answer
94 views

Where does it make sense to store Indexes for partitioned tables?

Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
2
votes
1answer
4k views

Do you set an index on JOIN clauses or where clauses, or both?

Say my query looks like: SELECT t1, t2 FROM t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid) WHERE t1.enabled = 1 AND t1.startDate <= ??? AND (t1.counter = -1 OR ...
1
vote
1answer
122 views

mysql dummy where check to utilize multi column index

I have multi-column index on col1,col2. Both columns are unsigned int with values 1 or greater. I want WHERE col2=100. I write WHERE col1>0 AND col2=100. The query should now use my index. Does ...
3
votes
2answers
1k views

SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help

I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema: CREATE TABLE Documents ( documentId bigint IDENTITY NOT NULL, content ...
4
votes
3answers
3k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
0
votes
1answer
60 views

Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise ...
4
votes
1answer
1k views

How does SQL Server choose an index key for a foreign key reference?

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
4
votes
2answers
6k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
0
votes
2answers
107 views

SQL and Multiple Indexes

Consider the following table: i | a | b | c -------------- 0 | 1 | 2 | 3 1 | 4 | 5 | 6 2 | 7 | 8 | 9 3 | x | y | z The table, in the end, will have several thousand rows, and even more columns then ...
3
votes
4answers
6k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
1
vote
1answer
3k views

How can I tell if an index is being used to sort in MySQL?

I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form: SELECT cols FROM tables WHERE ...
5
votes
1answer
625 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 ...