Solved

ReIndex Table

Posted on 2008-10-23
2
742 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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