Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to rebuild a primary index

Posted on 2006-11-01
10
Medium Priority
?
503 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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