Is it wise to delete mysql rows frequently?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Is it wise to delete mysql rows frequently?

Post by infomamun »

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
User avatar
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?

Post by social_experiment »

infomamun wrote:My question is if rows delete and create frequently, will it affect of that mysql tables/database?
I don't think it will adversely affect the database/ table because those actions contribute to the purpose of the 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
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Is it wise to delete mysql rows frequently?

Post by twinedev »

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:
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
The also give a list of what types of things may cause a table to be corrupted and needing repair on this page:
http://dev.mysql.com/doc/refman/5.1/en/crashing.html

-Greg
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Is it wise to delete mysql rows frequently?

Post by Benjamin »

I agree with twinedev.

I would consider using a memory table though: http://dev.mysql.com/doc/refman/5.0/en/ ... ngine.html
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: Is it wise to delete mysql rows frequently?

Post by infomamun »

Thanks all for your valuable comments and explanations. Specially twinedev illustrated it in easy manner and with practical examples.
Regards
Post Reply