[RESOLVED] redo auto increment?

djalecc

Is there a command to re-do auto increment ?

I have a databse with 3.7 million lines, and have deleted dups from it, leaving just 1.2 million lines, but now the next 'auto increment is 3.7 million.

This is an issue for me because I want to use either the TOP command to tell people how many entries we have, or I think there is a command to get & return the next auto increment number anyway.

What I want to do is re-set the auto increment in all the lines to 1.2.3.4.5.-......1.2million etc so there are no gaps.

Is there an easy way to do this guys ?

Thanks in advance if you can help.

🙂

bradgrafelman

Perhaps one of the DB guru's can correct me, but I don't think an AUTO_INCREMENT column should be used for counting purposes - it should simply be a unique identifier; re-creating these identifiers can break relationships as well as take time/resources.

If you're simply looking for the number of rows in a table, and you have a MyISAM table, you can do a "SELECT COUNT()". If you have an InnoDB table, this query will probably be slower, in which case you could simply cache the results of the COUNT() query.

EDIT: Forgot to add... an alternative for InnoDB tables that might be faster is:

SHOW TABLE STATUS LIKE 'tblName';

Otherwise, if you want to "re-index" your rows as you originally asked, you'd simply have to drop the AUTO_INCREMENT column and re-add it to the table.

djalecc

Yes its MySQL and MyISAM.

I use select max(id) from table_name on other tables to get the current number, which is much quicker than Count(*), this is the command I want to use, counting 1.2million every time that page is requested seems a bit of a waste of CPU power.

After I removed all the dups, It just seems easier to do it this way, or am I missing something simple here?

bradgrafelman

djalecc wrote:
counting 1.2million every time that page is requested seems a bit of a waste of CPU power.

You won't be counting rows, though, if you're using a MyISAM table - this number is cached.

djalecc wrote:
It just seems easier to do it this way, or am I missing something simple here?

As I said, it's considered a bad practice to use this field for any counting means, as if you alter the ID's then you break any relationships they formerly held.

djalecc

Yes I understand your point, but the ID's are not tied in to any relationships [ yet ].

I've spent a while on getting the data how I want it, and now its 'ready' for real life... point is now its ready, that will now be the official 'start' and everything will run smooth if I can just get the auto-increment to re-set in sequence!

🙂

bradgrafelman

Well, I explained a couple of posts up how to reset the AUTO_INCREMENT column, so I suppose if that's really what you want to do despite the alternatives, go for it. I still suggest that you start learning good practices now (even if you don't think you need them right now) rather than use bad habits and simply hope it works for the present.

Still... is there a reason why you can't simply do a "SELECT COUNT(*)" instead (or even a "SHOW TABLE STATUS")?

djalecc

Sorry I missed your edit, thanks! These late nights coding get to me some times!

As I explained, I just want the number to be 'correct', when dealing with such a large database, and want to save as much CPU as possible, once this has been done it's ready to go live and I can relax!

Thanks for the help.... 🙂

bradgrafelman

I would still suggest using COUNT() once you go live. If nothing else, you should at least do some benchmarks of COUNT() versus MAX(id).

Like I said, it never hurts to learn good practices now rather than later.

djalecc

Yes, I think I will... you mentioned it doesn't count .... I'm a little confused by that... can you expand on that more... sorry if that sounds dumb, its late and I've been at it all day ...

bradgrafelman

MyISAM tables cache the number of rows they hold, and COUNT(*) is optimized internally to use this cached data, returning a response almost instantly.

Here is an article that talks about using COUNT(*) to get the number of rows versus COUNT(col); it's not exactly applicable to your situation, but it still talks about the optimizations used.

EDIT: Decided to test COUNT() on my home PC. Note that this result is with other programs open in the background and accessing the hard drive (I think I might've even had a movie unzipping at the time, too):

mysql> SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|  1337414 |
+----------+
1 row in set (0.05 sec)

So, the first, unprimed COUNT(*) query took 0.05 seconds in a table with 1,337,414 rows. After that, successive queries returned:

1 row in set (0.00 sec)

djalecc

Cool... I get it now...

Anyway, I've done it! 🙂

Now to test...

Here is Max(id)

Query took 0.0003 sec

and Count(*)

🙂

OK, I stand corrected... 🙂

Many thanks!

Piranha

If you don't use MyISAM you can pretty easy get the number of rows by simply adding another table. Something like:

create table tablerows (
tablename varchar(50),
rows int
)

Then you can, depending on how exact it have to be, either update the row on each insert and delete or run a cron job once every minute/hour/month to update it. It is not a pretty way to count rows, it is a workaround. But if it is an extremely busy site with millions of rows in the table it is a possibility.