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.
2
votes
1answer
207 views
Index with multiple leaf levels
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
2
votes
3answers
413 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
2
votes
2answers
157 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?
2
votes
3answers
613 views
What impact do indexes have on the performance of table modifications?
I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table?
My gut ...
2
votes
2answers
71 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?
...
2
votes
3answers
91 views
mariadb ignoring force index
I'm trying to tune a relatively simple query:
select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
...
2
votes
1answer
112 views
Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included
I am currently experiencing the error (logged via traceID 1222 ):
keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S ...
2
votes
2answers
155 views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
3answers
440 views
SQL Server Timeout: Drop and Recreate Index Solves the Issue
After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having. The strange thing is ...
2
votes
2answers
128 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 ...
2
votes
1answer
295 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
...
2
votes
4answers
406 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 ...
2
votes
3answers
82 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 ...
2
votes
2answers
483 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
2
votes
1answer
139 views
Aren't two writes required to update a clustered index record
I was reading the article on indexes at simple-talk, where it is written that
If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
2
votes
2answers
359 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 ...
2
votes
1answer
893 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
60 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
233 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
257 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 ...
2
votes
3answers
673 views
SQL Server Index Fragmentation
When should you rebuild an index? vs reorganize?
What index fragmentation value is considered healthy?
2
votes
1answer
33 views
Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
2
votes
1answer
237 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
2
votes
2answers
205 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 ...
2
votes
4answers
444 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 ...
2
votes
1answer
373 views
creating an index for varchar column greater than 900 bytes
I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is ...
2
votes
2answers
66 views
Are these extra Index fields needed?
I've designed a table as below for users who register a quiz:
CREATE TABLE `registered_quiz` (
`rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`user_id` smallint(5) unsigned NOT NULL,
...
2
votes
2answers
1k 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 ...
2
votes
1answer
224 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.
...
2
votes
2answers
167 views
Execution plan doesn't use clustered index!
I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID.
I have created a clustered B+tree index on the field foodID of my table foo.
And I do the following:
...
2
votes
1answer
243 views
Creation of spatial index locks the table for hours
I used this code to create a spatial index in SQL Server 2008 R2:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ...
2
votes
2answers
237 views
How do redundant indexes impact query performance?
The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not?
How the redundant indexes are handled by ...
2
votes
2answers
192 views
Is there any index with O(1) complexity for lookup in PostgreSQL?
Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?
2
votes
2answers
163 views
In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?
For example, having a users a table and then ...
2
votes
1answer
136 views
What are hypothetical indexes?
They seem to be created by tools like DTA.
For practical purposes they could be deleted, ignored or chosen to be created, but until then, they have no direct influence on the database.
2
votes
2answers
309 views
Check Index Fragementation Query
I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea.
I have a new production SQL Server (Windows Server ...
2
votes
1answer
88 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 ...
2
votes
1answer
711 views
After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
1answer
160 views
Why 2 allocation units during online index rebuild to different file group?
I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
2
votes
1answer
420 views
How does postgres use the schema search path when making indexes?
Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app.
I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
2
votes
1answer
333 views
How are multiple indexes used in a query by MySQL?
Imagine a table where there are separate indexes on columns a and b.
The query runs somewhat like this:
SELECT col1, col2 FROM `table` WHERE a = 'value_1 AND b = 'value_2';
Now, does this query ...
2
votes
2answers
314 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
147 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 ...
2
votes
2answers
335 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 ...
2
votes
1answer
95 views
Are two indexes needed?
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.
Which indexes should I use? Should I have two ...
2
votes
1answer
214 views
Disabling Indexes and Auditing for large data import
Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are:
1.
Is it ...
2
votes
2answers
110 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 ...
2
votes
2answers
347 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 ...
2
votes
2answers
154 views
Rebuilding Index - sort in temp db
What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)?
Where else does sorting take place?
2
votes
2answers
251 views
What does “GIN doesn't support full index scans” mean?
I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be ...
