Solved

How to rebuild a primary index

Posted on 2006-11-01
10
488 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
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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 500 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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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