?
Solved

How to rebuild a primary index

Posted on 2006-11-01
10
Medium Priority
?
494 Views
Last Modified: 2008-02-01
Hi

I need to rebuild the primary index with auto increment to remove all the gaps. What is the correct way of doing this?

Is it

ALTER TABLE mytable DROP PRIMARY KEY

Please, advise.

Thanks
-A-
0
Comment
Question by:andy7789
[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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17848660
that command only drops the primary key, you have to add it then:

ALTER TABLE mytable ADD PRIMARY KEY ( column_name );
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17848664
Hi,

I would not advise doing this - the gaps do not necessarily mean anything and you should never rely on the numbers being indicative of anything (order) - just that they would be unique.

If you wanted to rebuild everything then dropping the index would be insufficient, you would be best dropping the whole field and index and then adding the field and index again.
0
 

Author Comment

by:andy7789
ID: 17848697
I use primary id as a sort of counter in the php cycle. Reducing the max number gives me better performance. If Ii drop the promary key what happens with auto increment? Should I also

ALTER TABLE tbl AUTO_INCREMENT = 1

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:andy7789
ID: 17848713
When I am trying to drop the promary key, I am getting an error:

"Incorrect table definition; there can be only one auto column and it must be defined as a key "

What should i do first? remove auto increment?
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17848887
I suspect what you really need is a better technique on the PHP side; relying on contiguous primary key values doesn't seem like a good idea for a variety of reasons.  Databases are pretty good at not caring about that sort of thing, so application code usually should be too.

I'd strongly suggest taking a different approach to this, as others have already.


If you're dead set on it, then the following will work:

ALTER TABLE the_table
  DROP PRIMARY KEY
, DROP COLUMN id
, ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST
;

This must be done in a single query, since any one of those actions by itself would be illegal.  You cannot remove the primary key from a table, you cannot add an AUTO_INCREMENT field to a table that already has a key, et cetera.  Doing them all at once, though, will work.
0
 

Author Comment

by:andy7789
ID: 17848980
Agreed - this is a weak part of the code. The problem is that I have a html form  - table generated from the database. The user can select any rows by ticking the appropriate checkboxes and submit the whole bunch of their IDs as POST. The only solution i could find is to assign a checkbox name to the row's id, i.e. <input type="checkbox" name="<? echo $row_p['id']; ?>". In brief, I am getting POST[id], where id is just a number.

For example, I may get POST[5], POST[125] and POST[1555]. Since those POSTs are not indexed, I have to cycle through all the numbers to extract just three numbers.

This is the reason, why I am getting the mess with table indexes, because I have to check those ID's across all possible values of the primary index.

If you have some better idea how to do this, I wold be more than happy to get rid of this messy technoique.
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 2000 total points
ID: 17849295
Ahhh, I see.  Sure, I can give an excellent solution to that.

Use this:

<input type="checkbox" name="row_p[<?= $row_p['id'] ?>]" />


This will generate names like name="row_p[5]"   (and row_p[125] and row_p[1555]).

PHP will, as you might hope, generate an array from this when it creates $_POST, so you'd write:

foreach($_POST['row_p'] as $key => $value) {
   // Here $key is the ID (5, 125, 1555)
   // $value is just the value of the checkbox, so probably 'on' -- you'll probably ignore it completely
}
0
 
LVL 4

Expert Comment

by:Sheeri
ID: 17868359
I agree with the above.  Primary keys should NOT be changed frequently.  I suggest adding a column called "php_counter" and let the php program manage that.  

If you insist on misusing auto_increment in the database to contain business logic, though, you would need to drop the auto_increment column and re-add it, as VoteyDisciple  said.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17870930
Have you been able to get th esoloutions working - by changing the php rather than rebuilding the index?
0
 

Author Comment

by:andy7789
ID: 17871018
Thanks a lot! I have got the VoteyDisciple' solution working with rebuilding another array as:

$sel_array = array();
if(isset($_POST['sel'])) {
    $i=0;
foreach($_POST['sel'] as $key=>$value) {
    $sel_array[$i] = $key;
    $i++;
}
}

In fact, I do not need even $value, as it is "Yes" for all the elements of the POST, because the inputs are checkboxes.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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