MYSQL question - size of field

[Blue Cat Buxton]

I have a table to store merchant offers that updates via a script - eventually every night.

The first thing the script does is delete all the existing offers from that supplier then pull in the current offers.

Each offer has an id which is auto incremented.

The total number of rows in the table is unlikely to get above a few thousand, but the id is already up to tens of thousands and will rise quickly to millions - will this cause any problems, or should i periodically drop and recreate the table, and zero the id column?

[Joachim]

An unsigned int goes up to 4,294,967,295 if that's not enough and
you have the option of clearing out the table I would suggest todo so.

While i'm not really a mysql guy, I assume mysql has similar ways to (re)analyze your indexes. If you have such a large volume of transactions it might be wise
to research that to get optimal performance out of your database.

The following command will clear the table's data and reset the sequence.
truncate table <tablename>;

[moonshield]

periodically drop and recreate the table, and zero the id column?

You really don't have to go that far, just use an UPDATE instead of an INSERT.

[Blue Cat Buxton]

An unsigned int goes up to 4,294,967,295


I guess it will take a while to get that high!



You really don't have to go that far, just use an UPDATE instead of an INSERT.

I cant update because I dont link/relate the entry to the merchants data. Data feeds seem all overthe place, no consistency so it is really difficult in getting the merchants data to match up with sensible field names, which is why I just delete the existing data and start from scratch.


periodically drop and recreate the table, and zero the id column?
I think I will do this, but i do need to lookinto database optimisation some more

Thanks for the replies

[Joachim]

Not sure if you got it but ....

There's no need to drop/recreate the table.

Just use the truncate table statement and your
auto-increment field will reset to zero.

[Blue Cat Buxton]

No missed that, cool, thanks