Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Comments for: tim20010110

Message # 1014703:
Date: 12/13/02 17:48
By: devlin
Subject: RE: Damaged Indexes

A damaged index is worse than having no index. No index just means queries will be slower, whereas a damaged index could cause a query to return incorrect results.

Repairing an index usually amounts to dropping and re-creating the index. This is something that should be done occasionally regardless of corruption. Most real DBMSs don't optimize their indexes every time a record is added (performance would be far worse than having no indexes), so dropping and re-creating them will create nice, optimized indexes. It usually takes a non-trivial amount of time to rebuild all indexes (depending on the number and size, of course), so it's generally not something to do often. For example, our databases have hundreds of indexes for hundreds of tables with tens of millions of records. We rebuild the indexes every three months and it takes about 3-4 hours to do.

Previous Message | Next Message


Comments:
How does indexing work ?Andy09/30/07 18:56
Large Database with frequent inserts and updaKrish05/19/05 08:03
RE: So what are indexes?Kenny G.04/29/04 19:56
Nice article!Fabien Papleux08/06/03 14:49
php pgm in doubtpravin01/27/03 05:55
RE: Damaged Indexesdevlin12/13/02 17:48
So what are indexes?Amos12/05/02 17:42
Damaged IndexesGeorge Esoimeme10/16/02 05:10
questionmadEr09/10/02 06:38
indexing and optimlzationMichel Schellekens08/12/02 06:05
RE: how to search 1 table with 350 columns ?Dan Reiland07/09/02 13:53
RE: WHERE col LIKE '%df%'Tim07/08/02 07:42
Dyanmic WHERE clausesJonathan Chum06/06/02 14:38
RE: mysql speedSon Nguyen05/18/02 17:22
WHERE col LIKE '%df%'A Martin05/15/02 18:26
how to search 1 table with 350 columns ?Pietro05/08/02 06:51
Creating Index for Simple MySQL Database mKarston04/23/02 11:57
mysql speedudayk04/07/02 03:27
Postgres optimization - hints neededPer03/22/02 03:04
IndexesLouise03/18/02 14:54
Databases IndexingHuilee03/07/02 21:29
winmysql connection problem.Jong03/04/02 00:00
Foreign Keys - indexed by default ?Sanjeev Deora02/26/02 15:41
What about selects with OR instead of ANDRyan Barnett01/22/02 10:46
exampleJ. M. Weggemans01/22/02 09:24
RE: K-Sensitive loginBob01/21/02 08:59
Re: Indexing - what if I add a row? James Puddicombe11/10/01 05:03
Indexing - what if I add a row?Jay Vincent11/08/01 11:23
RE: Optimization musings-comment threadssyco11/05/01 11:57
RE: K-Sensitive loginrichard09/24/01 17:42
K-Sensitive loginStorsh09/12/01 04:53
RE: EXPLAIN: MySQL vs. PostgresPatrick Massey06/25/01 13:44
EXPLAIN: MySQL vs. PostgresVincent Driessen05/06/01 15:06
RE: Newbie needs a litlle helpJimmiZ04/16/01 11:17
RE: Optimization musings-comment threadsBoon03/28/01 23:27
RE: Newbie needs a litlle helpKen Robson03/28/01 14:11
Newbie needs a litlle helpMaarten Verheijen03/25/01 12:04
Knowing whether mysql used your indexgreg03/17/01 16:39
Indexing a JOINNathan03/13/01 03:57
RE: Optimization musings-comment threadsPhillip Oertel03/02/01 15:06
RE: LEFT JOINs under MySQLrod k02/15/01 19:15
LEFT JOINs under MySQLspikeymikey02/15/01 16:54
Multiple ors in sql statementWes02/09/01 13:05
RE: multi-column indices and MySQL - Timrod k02/09/01 07:05
RE: multi-column indices and MySQL - TimBen Schumacher02/08/01 13:13
pgsql: CLUSTER commandTerence02/04/01 01:23
RE: comment threads - and other gripesTerence02/04/01 01:08
RE: Yay indicesYaron Yogev02/03/01 22:34
Yay indicesAnna01/31/01 18:37
RE: The how is well explained but why?doug mackenzie01/30/01 21:43
RE: multi-column indices and MySQLDon Baccus01/26/01 21:13
RE: Optimization musings-comment threadsGlenn 01/22/01 23:06
Prev - Next article linksWill01/22/01 08:22
RE: order of fieldsAnna01/19/01 14:37
RE: Optimization musings-comment threadsScott Molinari01/19/01 13:16
RE: order of fieldsChris Newman01/19/01 10:21
order of fieldsAnna01/19/01 00:16
RE: The how is well explained but why?Anna01/18/01 20:28
RE: Optimization musings-comment threadsTim Perdue, PHPBuilder.com01/18/01 13:39
RE: Optimization musings-comment threadsScott Molinari01/18/01 13:04
The how is well explained but why?Scott Molinari01/18/01 12:40
RE: multi-column indices and MySQL - TimAlan L01/15/01 09:34
cardinalityYaron Yogev01/14/01 09:23
RE: Index: What for ?Yaron Yogev01/14/01 09:01
RE: Optimization musingsBrian01/13/01 20:10
RE: primary key == index ?Baruch Even01/12/01 16:36
primary key == index ?juozas salna01/12/01 13:18
RE: You need to know your dataChris Newman01/12/01 07:29
RE: Optimization musingsBaruch Even01/11/01 16:51
RE: Optimization musingsKirk Parker01/11/01 15:07
RE: Optimization musingsBaruch Even01/11/01 12:49
RE: Optimization musingsKirk Parker01/11/01 00:40
RE: What about MySQL? - RodBrian01/10/01 19:10
MySQL vs. PostgresJeremy Rempel01/10/01 19:05
RE: multi-column indices and MySQL - TimTim Perdue, PHPBuilder.com01/10/01 17:37
RE: What about MySQL? - Brianrod k01/10/01 17:31
RE: multi-column indices and MySQL - Timrod k01/10/01 17:29
RE: Optimization musingsBrian01/10/01 12:52
Index: What for ?Anderson Fortaleza01/10/01 12:48
Optimization musingsBaruch Even01/10/01 06:31
RE: Order descending without sorting?Andrew Coldham01/10/01 02:34
RE: What about MySQL?curt01/10/01 00:33
RE: What about MySQL?Tim Perdue, PHPBuilder.com01/09/01 20:34
Order descending without sorting?Mats01/09/01 19:14
RE: What about MySQL?curt01/09/01 17:41
RE: A little cautionBjörn Brändewall01/09/01 16:34
You need to know your dataFrank Feingold01/09/01 12:37
What about MySQL?Brian01/09/01 12:06
RE: um...Paul K Egell-Johnsen01/09/01 11:07
um...Chris Snyder01/09/01 10:05
RE: A little cautionTim Perdue, PHPBuilder.com01/09/01 05:57
Confused one..Arni01/09/01 03:57
Indexes can be temporaryPeter Moulding01/09/01 02:26
RE: A little cautionKirk Parker01/09/01 00:39
RE: multi-column indices and MySQLTim Perdue, PHPBuilder.com01/08/01 23:00
RE: hmm...Tim Perdue, PHPBuilder.com01/08/01 22:59
RE: A little cautionTim Perdue, PHPBuilder.com01/08/01 22:57
multi-column indices and MySQLrod k01/08/01 22:50
A little cautionKirk Parker01/08/01 22:27
hmm...Andrew01/08/01 22:12
 

If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly.

Add A Comment:

Name:

Email:

Subject:

Message:

To reduce spam posts, messages are now manually approved

You are not [logged in]. That means your account will not get credit for this post.