Solved

ReIndex Table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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