Re-index MySQL Table

[VxJasonxV]

Is there any way to reclaim lost number in my Primary Key? I've deleted a few people from a database, but I don't what number gaps.

I just checked, repaired, optimized, and analyzed it, and nothing changed.

In short, Is there any way to re-index a table?

[Sardtok]

Well,
for postgresql (don't think it's available for mysql though, but I'll post it anyway) there's an own REINDEX TABLE indexname command.

Anyway,
I read somewhere that you can drop and recreate an index to get it fixed,
not sure how safe this is, I'd do a backup first... ;)

[VxJasonxV]

So. In phpMyAdmin. Drop the PRIMARY index (at the bottom), and re-PRIMARY the field?

...irk.


No workie that way. Any other ideas?

[Sardtok]

I believe they meant (scary as it may sound),
drop the entire index field,
then re-add the field with the auto-index and all...

That's why I said you should do a back-up first,
if you did this, then I'll have another try at google... ;)

[qingshuo]

Could be a bit dangerous, since relations can get all screwed up. I had this problem with my b2 database, and I just wrote a script that re-index everything including relational fields in other tables.

[VxJasonxV]

There are no relations at this time. It's a completely independant table.

[Marble]

Im not 100% sure about mySQL, but in msSQL if you let the index increment (auto increment) and once you lose a record it deletes that index number and doesnt replace it. You might run into major issues trying to delete a primary key or index and then replacing it, if the db will let you. Does it matter why you need to fill in those missing indexes?

[VxJasonxV]

It's not crucial that the order is preserved...but it'll help.

Basically, I have a list of players. And a bracket is SOOOO much easier to build is I have 1-32 instead of 1-14, 16-18, 20-29, 31 & 32.

Just looking for a way to ease the work load.

[Sardtok]

You shouldn't use the index numbers to enumerate like that.
Instead use the count rows system to check the total number of rows,
and then use the limit offset using the index to order them.

That is if I understand the problem correctly...
Let the retrieved array indices be the number of the item (remember to add the offset and possibly 1 as well to avoid getting item #0)... ;)

[VxJasonxV]

I'll do that for sure when I convert it to an offline table.

[Sardtok]

Also remember,
if you are talking about products,
don't use the array indices as product ids hehe ;)

But use them for enumerating it as a table to know what to show next or what number it has in some kind of record like a table showing news and you can view the news you want... ;)