Tagged Questions

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

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
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 ...
2
votes
2answers
300 views

Search on part of a column in a SQL Server cross-database view

I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
5
votes
1answer
1k views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
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 ...
7
votes
1answer
5k views

Does mysqldump export indices, by default?

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option: --disable-keys, -K which suggests, ...
8
votes
3answers
6k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
7
votes
3answers
653 views

Do I need to add a new single column index to a table if a multi-column index on that field already exists?

I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__. Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that ...
20
votes
4answers
6k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
4
votes
2answers
6k views

SQL Server: index creation date

In SQL Server 2005 and above, how can I find when an index was created?
5
votes
1answer
725 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 ...
4
votes
1answer
1k views

Indexing to improve performance of range queries

I've got a relation in a Postgres 8.2 database similar to this: CREATE TABLE foo ( foo_id varchar(160) NOT NULL, bar_id varchar(160) NOT NULL, created bigint NOT NULL, PRIMARY KEY ...
9
votes
5answers
3k views

When should nonclustered indexes be stored on separate filegroups?

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the ...
3
votes
1answer
4k views

How do I alter index rebuild an entire database?

I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command: ALTER INDEX all ON db REBUILD And I get the following error: Cannot find ...
7
votes
1answer
531 views

B-tree node split strategy in SQL Server for monotonically increasing value

Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% ...
4
votes
2answers
2k views

SQL Server 2005 Index Rebuild/Reorganize

I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing ...
2
votes
2answers
263 views

Best index strategies for read-only table?

I have a table in SQL Server which has following characteristics: will contain about 1.2M records. first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each ...
6
votes
1answer
3k views

PostgreSQL: COUNT(*) uses a sequential scan, not index

Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?
8
votes
2answers
4k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
6
votes
3answers
711 views

Multidimensional indices in databases

Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about ...
10
votes
3answers
4k views

How do indices impact query performance?

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
3
votes
1answer
666 views

Which fillfactor is used by ALTER INDEX REBUILD?

From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen. For tables I know to get rows added ...
2
votes
3answers
2k views

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?
5
votes
1answer
482 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 ...
9
votes
5answers
3k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
5
votes
3answers
388 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 ...
4
votes
3answers
1k views

Detect Index Coalesce

Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes. Note: I am not referring to the COALESCE ...
5
votes
4answers
4k 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 ...
17
votes
3answers
5k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
12
votes
2answers
1k views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
8
votes
2answers
1k views

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...
17
votes
4answers
624 views

Is "CREATE INDEX` in MySQL a Linear Operation?

What I mean is the following: If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time. What I'm trying to achieve ...
12
votes
2answers
2k views

Can I create index on a table in a MySQL database while in use

And if I can, any reason (performance/stability) why I shouldn't?
9
votes
4answers
819 views

Where should one put indexes in a time dimension table?

After reading the Questions and Answers from this website about indexes, a question came to my mind. What if, one is using a time dimension table with the lower level of granularity being the day. ...
5
votes
1answer
490 views

Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help. I can see that adding a filtered index to an employees ...
18
votes
2answers
845 views

Do I need separate indexes for each type of query, or will one multi-column index work?

I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic. My basic understanding is that generally speaking, a single index that ...
4
votes
2answers
164 views

How to create multiple entries on index based on the fields of one row?

I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL. The problem arises when you have fields working as tags or similar concept. Ex.: ...
39
votes
5answers
11k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
8
votes
2answers
832 views

How much “Padding” do I put on my indexes?

How do you know how much "Padding" an index should have?
54
votes
6answers
22k views

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
21
votes
4answers
3k views

What does “index” mean on RDBMSs? [closed]

I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation ...
1
vote
2answers
262 views

What is wrong in debunking myths about clustered indexes?

What is wrong in code. or its results, illustrating that clustered index is evil [1]? and how to de-debunk, i.e. to return to customary myths and best practices? [1] Debunking myths about ...
7
votes
2answers
377 views

Clustered indexing is now must - why?

Earlier, there were non-conclusive to me debates/discussions on whether to (always) engage/avoid clustered indexes. Well, I understood that they are to be used sometimes with proper + specific ...
5
votes
3answers
98 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 ...
8
votes
2answers
459 views

SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

On SQL server 2005, if all the databases are in Full mode (with hourly transaction log backups), is it possible to determine if rebuilding all indexes of a database can grow log file of a database? ...