Reset id auto_increment and then compare maximal id vs rows quantity

Question

I have table autos, In table is column id as auto_increment AND indexed as primary key

I want reset id, that is I need that ids will be queue from 1 to 2,3,4,5,...

For reset id, I run this query:

ALTER TABLE autos AUTO_INCREMENT = 1

This means that now, maximal id must be equivalent rows count right?

But this code

    $res = $db->query("SELECT COUNT(*) AS cnt FROM autos")->fetchAll(PDO::FETCH_ASSOC);
    echo "rows count: ".$res[0]['cnt']."<br>";


    $res = $db->query("SELECT MAX(id) AS id FROM autos")->fetchAll(PDO::FETCH_ASSOC);
    echo "max id".$res[0]['id'];

returns

rows count: 376733
max id: 500000

Why max id and rows quantity not matches? where I am wrong?

Duplicate of 1000s. **You don't need to reset auto_increment id**. Period. — Your Common Sense, Apr 03 '13 at 13:44
@ Your Common Sense --- I need make this, another thing is why this not works? what is reason? — Oto Shavadze, Apr 03 '13 at 13:49
duplicate of http://stackoverflow.com/questions/1824600/fragmentation-of-ids-auto-increment-column-in-mysql — Your Common Sense, Apr 03 '13 at 13:58
and also of http://stackoverflow.com/questions/3132439/mysql-auto-decrementing-value — Your Common Sense, Apr 03 '13 at 13:59
You don't need to reset auto_increment id. You just *think* that you need it, but actually you need something else. Please don't ask [an XY problem question](http://meta.stackexchange.com/a/66378). Describe a real life problem you face and get a **proper** solution. — Your Common Sense, Apr 03 '13 at 14:11
@Your Common Sense --- thanks for this http://stackoverflow.com/questions/1824600/fragmentation-of-ids-auto-increment-column-in-mysql — Oto Shavadze, Apr 03 '13 at 14:12

score 1 · Answer 1 · answered Apr 03 '13 at 13:47

I would never update the ID field like that, you will destroy any relationship with other tables (if you have them) and cause a lot of mess. You should make a new field SortOrder or something that is not a primary key, and then update this field to the new row number.

score -2 · Answer 2 · answered Apr 03 '13 at 13:45

-2

COUNT function will return number of rows in your table while MAX function returns MAX value a column has in your table.

For example, if you have only one record in your table with ID value 5000, then count will return 1 while MAX(ID) will return 5000.

While inserting record, if you do not provide value for auto increment column OR provide NULL value, then MySQL will assign the AUTO-INCREMENT count to this auto increment column and increase the pointer.

answered Apr 03 '13 at 13:45

Amit

1,365
8
15

`For example, if you have only one record in your table with ID value 5000` --- But after reseting ID auto_increment, ID value must be 1 and not 5000, right? – Oto Shavadze Apr 03 '13 at 13:50
Yes, now another insert will assign ID 1 if it is available, but MAX is an mathematical function which returns MAX value of column whether it is an auto increment column or normal column. – Amit Apr 03 '13 at 13:52

Reset id auto_increment and then compare maximal id vs rows quantity

2 Answers2