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
2answers
345 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
3
votes
1answer
430 views

mysql: need help to optimize my query/table

I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
3
votes
2answers
289 views

Reducing Log Impact During Re-Indexing

We use Ola's maintenance solution and its great. Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB ...
2
votes
1answer
132 views

Validate Primary Key and Index Selection

I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
1answer
214 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
2
votes
1answer
60 views

Do I need to reenter old data after adding an index to a table?

I want to add index to my tables. Some of my tables already have couple thousand rows. Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
4
votes
2answers
2k views

Suspended in index rebuild

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: state = ...
5
votes
1answer
293 views

When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 ...
1
vote
1answer
250 views

SQL Index order and performance based on cardinality and data

I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting ...
10
votes
3answers
11k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
7
votes
3answers
764 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
4
votes
1answer
262 views

How can I alter the fill factor of an index outside of an ALTER INDEX command?

I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
4
votes
2answers
1k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
6
votes
2answers
2k 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 ...
1
vote
1answer
100 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
4
votes
1answer
3k views

Why is a hash match operator in this very basic query

I'm beginning to learn some about looking at execution plans and making queries more efficient Consider these two basic queries select distinct pat_id, drug_class, drug_name from rx select pat_id, ...
5
votes
2answers
702 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
1
vote
3answers
3k views

SQL Server 2012, rebuild not lowering avg fragmentation

I have a script identifying what indices to rebuild. select 'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;' from sys.dm_db_index_physical_stats ...
2
votes
1answer
59 views

What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this? SELECT id FROM table WHERE table.int_col > 1; I'm specifically interested in ...
5
votes
1answer
419 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
3
votes
3answers
708 views

Programmatically find indexes that cannot be rebuilt online

I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included. Is ...
3
votes
2answers
204 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
2
votes
2answers
661 views

Differences Between Two Different Create Index Commands

Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script? ...
6
votes
1answer
309 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 ...
4
votes
2answers
1k views

Index before or after bulk load using load infile?

I have a database with over 1B rows and two columns that are indexed (in addition to the PK). Is it better to have the index pre-defined in the table before the load infile or better to index after ...
5
votes
2answers
606 views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
0
votes
2answers
261 views

Unique indexed column in where clause and mysql execution after finding first row

Suppose in a table there are 2 columns: login and password login is indexed as unique index, password not indexed. query: SELECT * FROM mytable WHERE login = 'Jhon' query: SELECT * FROM mytable ...
5
votes
1answer
262 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 ...
1
vote
1answer
2k views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
4
votes
1answer
178 views

After Rackspace server creation, PostgreSQL query planner doesn't work as expected

We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
6
votes
2answers
3k views

What are the differences between leaf and non-leaf pages?

I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
0
votes
1answer
67 views

Index use and column type

I was told today that having a table structure similar to this is bad for performance of index's because of the mismatch of column types Table 1 id - int (7) PK Others .... Table 2 id int (11) ...
3
votes
1answer
580 views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
4
votes
1answer
973 views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
0
votes
3answers
378 views

Mysql - How to Optimize Retrieval Time in a Table

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table... mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3; ...
1
vote
1answer
731 views

SQL Azure: Drop Constraint and Index

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake. Here is my question: When dropping Constraints, are their indexes dropped as well? ...
1
vote
1answer
166 views

I would like to query a range of criteria on multiple columns in MySQL

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example : We have 3 columns as ...
4
votes
1answer
2k views

Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field

This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
1
vote
1answer
1k views

Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
3
votes
0answers
159 views

Key Lookup and Full-text index

I have the following query: SELECT T2.Title FROM TitleTable T1 INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1 WHERE T1.FKID2 = @ID_PARAM1 AND T2.FKID2 = @ID_PARAM2 AND ...
0
votes
1answer
165 views

What is the maximum number of rows in a clustered index on datetime column?

I would like know what the max no of rows is in a clustered index (non-unique) on a datetime column table in SQL Server 2008R2.
1
vote
1answer
331 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?
2
votes
1answer
172 views

Will a primary key be added as a clustered index?

I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables. If I perform an alter table to assign a primary key will this ...
1
vote
1answer
147 views

Consolidating indexes

I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
11
votes
1answer
8k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
2
votes
1answer
448 views

How to improve INSERT speed in large table that has unique constraint

I have a simple MyISAM table: explain entities; +------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra ...
6
votes
1answer
184 views

Is there any reason to use index for very small tables (up to 1000 rows)?

During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, ...
0
votes
1answer
43 views

Which text-index I should create for xooops engine to achieve better search results?

In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following : SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
7
votes
2answers
983 views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
2
votes
0answers
72 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...