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)

5
votes
1answer
2k views

How to index dynamic attributes in MongoDB

I have following kind of data (simplified a bit from my real case) in MongoDB: { "name":"some name", "attrs":[ {"n":"subject","v":"Some subject"}, {"n":"description","v":"Some ...
1
vote
2answers
248 views

Is it possible to have indexes for like '%*%' type queries

Now for my thesis project i need to make a lot of queries like the below select * from tblMyTable where ItemName like '%item%' Now my question : is that possible to create indexes for such queries ...
2
votes
2answers
72 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
2
votes
2answers
287 views

Space Consumption for SQL Index Rebuild

I am attempting to rebuild the indexes for a single table on a SQL Server 2005 reporting database. The indexes are heavily fragmented as this has not been done for a long time. I am running the ...
10
votes
2answers
3k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
4
votes
1answer
136 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
2
votes
2answers
2k views

How can Oracle use a Range Scan here?

I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows. On this table, I have the index I_LOC_NAME_DATE, such that: Column ...
0
votes
1answer
506 views

MySql - Global index

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table: CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, ...
3
votes
1answer
317 views

SQL Server: Duplicate Index Question

We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...
2
votes
2answers
4k views

When to create multiple-column index?

When to create multiple-column index in Mysql? Should I measure the slow queries and create multiple-column index for them?
3
votes
1answer
139 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
4
votes
4answers
986 views

How are foreign key indexes helpful?

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
17
votes
3answers
6k views

Painless way to create a clustered index on a huge table?

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
3
votes
2answers
625 views

Performance impact of having gaps in Identity Column in SQL Server 2005

We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there ...
4
votes
2answers
1k views

How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

I'm working on a project that must support two database engines; SQL Server and PostgreSQL. We are using NHibernate as the ORM. We are running into performance issues with certain queries. Using ...
2
votes
2answers
308 views

MySql - find redundant indexes

is there a tool that spots redundant indexes in MySql? e.g. if I have the following indexes: index1(col1) index2(col1,col2) then index1 should be flagged as redundant. any ideas?
3
votes
1answer
581 views

MySQL text field without quote

I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table: mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = ...
4
votes
2answers
833 views

Less RAM than Index_length MyISAM

I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
3
votes
1answer
151 views

should we replace some non-clustered indexes with clustered indexes

We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk. I am thinking that ...
2
votes
1answer
238 views

What's a good indexing scheme for a type-2 SCD table with a very broad natural key?

I have a table which links several fairly long text fields (~100 chars) to a single text field: { A, B, C, D } => E. The dependent value can change, so I query data daily and record A, B, C, D, ...
2
votes
2answers
2k views

Can I add “Included Columns” to an index without affecting performance?

I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly ...
7
votes
2answers
6k views

What is the difference between ALTER INDEX and DBCC DBREINDEX?

Is the only difference between ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90) and DBCC DBREINDEX([dbname], 90) just that the DBCC command will reindex all ...
3
votes
1answer
519 views

Online Re-Indexing In SQL Server 2005/2008 Standard

The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition. ...
1
vote
1answer
2k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...
3
votes
1answer
1k views

create index - invalid memory alloc request size

i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index. The Table looks like this: CREATE TABLE schema.tblname ( id bigint NOT ...
6
votes
2answers
2k views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
14
votes
1answer
3k views

How do databases store index key values (on-disk) for variable length fields?

Context This question pertains to the low-level implementation details of indexes in both SQL and NoSQL database systems. Actual structure of the index (B+ tree, hash, SSTable, etc.) is irrelevant as ...
1
vote
2answers
1k views

All columns of table should be indexed or not mysql database?

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
13
votes
2answers
13k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
7
votes
4answers
16k views

When to add indexes to #temp tables?

When testing stored procedures in SSMS, it sometimes says there is a missing index on #someTempTable____________________000000000000005B] (someField) etc etc When I do add them like this to the sp: ...
17
votes
3answers
2k views

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
4
votes
2answers
3k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
7
votes
2answers
2k views

Defragmentation - Rebuilding Indexes SQL Server 2005

I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for: ALTER INDEX ALL ON mytablename REBUILD WITH(ONLINE = ON) When I pull the info from ...
4
votes
2answers
211 views

MySQL: Is it safe to have a table with multiple millions of rows?

I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once ...
2
votes
1answer
116 views

Selection of Indexes in SQL Query

I have a table with following 3 fields 1) Id bigint 2) Name varchar(100) 2) Country varchar(50) 3) State varchar(50) 4) City varchar(50) In a country there are no. of states and in a ...
2
votes
1answer
7k views

How can I optimize my mysql setup to create my index faster?

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored. I have an MyISAM like ...
2
votes
1answer
4k views

How long will an index take to create?

I have a table with over 70 million rows in and I have viewed the execution plan which has identified a missing index. I took a snapshot of data a short while ago and created this index on a local ...
3
votes
4answers
2k views

SQL Server Indexing Performance in Unique/Duplicate column

It is said that if we create index on a column with more uniqueness than performance of that index will be more. But I believe that whether it is unique or not it will occupy same no. of blocks than ...
0
votes
1answer
155 views

oracle local partition index

I have a table like this: CREATE TABLE employees (employee_id NUMBER(4) NOT NULL, last_name VARCHAR2(10), department_id NUMBER(2)) PARTITION BY RANGE (department_id) (PARTITION employees_part1 ...
0
votes
1answer
287 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
45
votes
5answers
49k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
1
vote
2answers
2k views

oracle global index vs local index on a partitioned table

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
6
votes
4answers
3k views

How does SQL Server reduce index fragmentation?

If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes? MSDN suggests that if an index is over 30% fragmented, it is recommended to run ...
3
votes
1answer
953 views

Index fragmentation increase as more row being deleted

I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low. This table has high transaction throughput. ...
14
votes
1answer
1k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
3
votes
1answer
3k views

Oracle not using index when > used in where clause

We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date. Because there is no end clause to the date range, oracle ...
4
votes
2answers
4k views

MySql - How can I speed up this query

I have the following tables: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(255) COLLATE ...
16
votes
2answers
2k views

Primary key index with a DATETIME as first part of the compound key is never used

I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY. I use MySQL 5.5 Here are my two tables: -- This is my standard table with dateDim as a dateTime CREATE ...
0
votes
1answer
258 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
3
votes
2answers
7k views

How to make CREATE INDEX in InnoDB table faster

I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time: Time | State | Info 2291 | copy to tmp ...