Rows missing!

delfa

Hi php users!

Tell me something, when you delete a row from mysql database, for example ID nr. 200, than you insert another information, mysql creates another ID nr 201.

If you keep doing that every day the database becomes fragmented. You know, many ID are missing because you don´t need them and deleted them.

How do avoid/correct that ?

Thanks for any help!

TTT

$q = select max(id) as id from table
$new_id = odbc_result($q,1) +1;

databese doesn't defragment 🙂 ... i don't get it .. what's the problem with missing id's? i think the best solution is that you create table called table_sys, fields : table name, current value.
with this table you can manage all tables current values.

Wave

$q = select max(id) as id from table
$new_id = odbc_result($q,1) +1;

even if forget about twisted sintax, this construction is meaningless.
what if you have 200 rows, and delete row number 100?

i think the best solution is that you create table called table_sys, fields : table name, current value.
with this table you can manage all tables current values.

weird solution. as weird, as i am 🙂

mysql take a care about autoincremented id's automatically.
IMO there isn't any reason to create additional table to store things, wich we can simply get with LAST_INSERT_ID()

If you keep doing that every day the database becomes fragmented

don't worry and relax. this kind of fragmentation is normal. defragmentation can make more hurt then pleasure.
f.e. if you have rows with id 1, 2, 4, 5 and you wish to make em defragmented, so you set id 4=3 and 5=4
OK, you get what you want, but if row with id 4 referenced from another table you get your database breaked out.
if you mean phisical fragmentation, you should read about ANALYZE TABLE statement.

thar's all what comes in my mind now.

Regards, Dan.

m7_b5

Basically what everyone is talking about here is the simple concept of NORMALIZATION.

I agree with the post that defragmentation is nothing to worry about. In a flat file system, this is no problem. Gaps in your IDs are perfectly common.

However, in a related database system you must be careful with deleting records where that ID is foriegn key in another table or you will end up with incorrect record matching later.

My suggestion is that if you are using a relational database you keep a function around that makes sure any related records are removed from dependent tables when you remove a row from the parent. That will help keep your data in order.

An interesting question: Lets take this out to say he has 200,000 records in his table +. At some point does mySQL take LONGER to query the higher numbers? It would seem to me that there shouldbe no difference but that might possibly be the only reason for re-indexing your IDs in a table.

M7_B5

delfa

Thanks Wave!

That script seams to work!

No. I´m not talking about physical fragmentation, I was just using that term to say that some ID´s are missing.

Yes, you´re right m7_b5, I am talking about that weird word 'normalization'.

[deleted]

Yes the missing numbers are perfectly normal, and no you should not ever try to do anything about it, least of all the infamous MAX(column)+1, because that is just plain wrong in a multi-user environment. It can only work reliably if you exclusively lock the table for that particular user, locking out all other users untill this one is finished.

About the referential integrety, that's why real databases have a foreign-key relation that prevents you from deleting records that are still being referenced from another table. Crappy mysql doen't believe this to be important so they don't support it.

In real databases such as postgresql, oracle, informix and db2 you can also use a 'CASCADING DELETE'. Then when you delete a row from one table, the database automatically deletes all rows that point to the row you want to delete.

m7_b5

Vincente,

I agree referential integrity rocks. I would also add MS SQL and MS Access to the list of real databases that support it.

M7_B5 (ducking)

[deleted]

MS-SQL yes, sort of :rolleyes: , but MS-Access most definately not :mad: