Solved

ReIndex Table

Posted on 2008-10-23
2
744 Views
Last Modified: 2008-10-24
In a table I have following structure:

Item ID        Int          Primary Key
ItemName       varchar
ItemCode       varchar

The database in already in production environment and containing many records. Now I have decided to add another index on the column Item name. I want to know whether I need to rebuild index (using any command) to refresh indexes internally or simply just add the new index.
0
Comment
Question by:rpkhare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
msklizmantas earned 65 total points
ID: 22794280
hi,

most of the time rebuilding indexes is not needed in mysql, but you could use this sql statement:  

OPTIMIZE TABLE yourtablename;

it will see if the table has overhead and fix it, also it will sort index if there is such need and will rebuild statistics.

regards,
m
0
 
LVL 12

Assisted Solution

by:AdrianSRU
AdrianSRU earned 60 total points
ID: 22795907
Optimizing the table on a regular basis is always a good idea.  It keeps the indices working as efficiently as possible.  For adding new ones you can just run the add index command.  There is nothing special that you need to do to rebuild or refresh the table.

If the table is very large you may want to wait until a low traffic period before adding the new index or optimizing the table.  It can take anywhere from a few seconds to a few minutes to perform the operation and the table will be locked out from any other reads or writes during that time.


--Adrian
0

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question