Solved

ReIndex Table

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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