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)

3
votes
1answer
2k views

How can I set the index fill factor back to server default?

I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command: alter index all on dbo.Table rebuild with (fillfactor=80) That ...
2
votes
1answer
211 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', '...
3
votes
1answer
847 views

Error while creating index in SQL Azure

I'm trying to create an index on table [Children] in SQL Azure but get the following error: The operation failed because an index or statistics with name 'IX_Children_ParentID' already exists on ...
9
votes
1answer
855 views

Why does the fill factor in SQL Server default to 0 (100%)?

I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95? Is ...
2
votes
1answer
691 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
5
votes
2answers
172 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 ASC)...
3
votes
1answer
221 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
3
votes
2answers
229 views

What is the signifigance of a fillfactor of 20?

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
12
votes
1answer
1k views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
6
votes
2answers
2k views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
6
votes
2answers
5k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
3
votes
1answer
469 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
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 ...
4
votes
1answer
858 views

Why does it take a long time to drop a function based index in Oracle?

I have a function based index that takes 25 minutes to create on a table of 94 Million rows. When I drop the index, it takes 18 minutes. Why does it take so long? I would have thought the drop would ...
4
votes
2answers
5k views

Reindexing a large database

I have a very large database (millions of rows) which has been indexed. I receive a diff of rows from another source that I need to update in my database. The problem I am facing is that is it ...
4
votes
2answers
811 views

Computed column with less size than the “referring column”

I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
6
votes
1answer
990 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
5
votes
2answers
3k 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
650 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, ...
3
votes
1answer
2k views

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing: We're writing some code to migrate our messages to a new table called communications. We're ...
2
votes
1answer
2k views

Best way to compare strings using pre-built ngrams?

I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
26
votes
1answer
47k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
4
votes
1answer
515 views

index not being used

I have an exact same myisam table on master and slave. The indexes are not being used on Master server. mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = '...
4
votes
2answers
1k views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
7
votes
3answers
516 views

Best practices for change management with indexes

Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us. ...
6
votes
1answer
7k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
2answers
383 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
2
votes
2answers
359 views

Does the number of indexes effect the modification of table?

First of all Good morning ! Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !! Now come to the point, ...
1
vote
1answer
920 views

Create Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs ...
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
73 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
296 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
139 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
514 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
318 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
1k 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
628 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
310 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
590 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
837 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
155 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
245 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 ...