Solved

ReIndex Table

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now