Hi there,
I have user login system in my website. For implementing secure login system and also for browser specific login system, it is need to delete mysql table rows frequently as users logout.
Also a new row will be created frequently while a user logs in to my website.
I can't maintain row for each user permanently because a user can login from 4/5 browsers and for each browser it needs to create one row. So If I have 500 or more users, in that case for faster searching, I don't want to keep rows which has no data about user login (e.g user logs out from a browser, so I don't need to keep information of that browser any more).
My question is if rows delete and create frequently, will it affect of that mysql tables/database?
And in that case, should I have to repair that table periodically?
Any help will be appreciated.
Best Regards
Is it wise to delete mysql rows frequently?
Moderator: General Moderators
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Is it wise to delete mysql rows frequently?
I don't think it will adversely affect the database/ table because those actions contribute to the purpose of the database.infomamun wrote:My question is if rows delete and create frequently, will it affect of that mysql tables/database?
I did a search for 'when to repair a database / table' and only found 'how to' results; personally i haven't had an instance where i had to repair a db so i'm not very educated on the specific subject.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Is it wise to delete mysql rows frequently?
I think what you are thinking of is Optimizing the table.
Repair, you hardly need unless you do detect an issue, at least for me, that it the only time I have ever used them
When you have say 50 rows, (as in raw data positions, not ID field values), and you issue a command to say delete the row that is in position 20, technically you still have 50 rows used up in for the database file, #20 is just set to be able to take the next new row inserted.
Do an optimize will remove all the "empty" rows, so in the above example, if you got rid of #20, optimized, now the file will actually only have 49 rows.
Now you may think "Well I'm constantly deleting them, so will there be a ton of gaps?" Well also consider how often the new rows are created. Those new rows will fill up the empty spots from the deleted ones.
Also another time to do this is say you had a table that had a varchar(100) field, and you realize that you only needed 50 after all so you go change the field size. Until you optimize, there will be empty space for each row.
From the mySQL manual on REPAIR:
http://dev.mysql.com/doc/refman/5.1/en/crashing.html
-Greg
Repair, you hardly need unless you do detect an issue, at least for me, that it the only time I have ever used them
When you have say 50 rows, (as in raw data positions, not ID field values), and you issue a command to say delete the row that is in position 20, technically you still have 50 rows used up in for the database file, #20 is just set to be able to take the next new row inserted.
Do an optimize will remove all the "empty" rows, so in the above example, if you got rid of #20, optimized, now the file will actually only have 49 rows.
Now you may think "Well I'm constantly deleting them, so will there be a ton of gaps?" Well also consider how often the new rows are created. Those new rows will fill up the empty spots from the deleted ones.
Also another time to do this is say you had a table that had a varchar(100) field, and you realize that you only needed 50 after all so you go change the field size. Until you optimize, there will be empty space for each row.
From the mySQL manual on REPAIR:
The also give a list of what types of things may cause a table to be corrupted and needing repair on this page:Normally, you should never have to run REPAIR TABLE. However, if disaster strikes, this statement is very likely to get back all your data from a MyISAM table. If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE
http://dev.mysql.com/doc/refman/5.1/en/crashing.html
-Greg
Re: Is it wise to delete mysql rows frequently?
I agree with twinedev.
I would consider using a memory table though: http://dev.mysql.com/doc/refman/5.0/en/ ... ngine.html
I would consider using a memory table though: http://dev.mysql.com/doc/refman/5.0/en/ ... ngine.html
Re: Is it wise to delete mysql rows frequently?
Thanks all for your valuable comments and explanations. Specially twinedev illustrated it in easy manner and with practical examples.
Regards
Regards