MySQL overheads, is my query wrong or should I Optimise the table?

Question

I have a table setup like below.

Table comment_flags
    user_id
    comment_id

I allow users to flag a comment and then give them the option to unflag it, because they may have made a mistake.

The problem is that when they unflag a comment, after I delete the record , the comment_flags table get roughly 30-40bytes of overhead, even after only unflagging 5 comments.

Heres basically whats going on:-

A user flags a comment, so I insert user_id and comment_id into comment_flags table, then the user unflags the comment and I delete from the comment_flags table by the user_id and comment_id.

I am not sure that it's my SQL thats causing the problem though, I think it's just the DELETE statement in general.

I could optimise the table after every DELETE statement to clean up the table.

Is this seen as a good idea? How does everyone else deal with this?

My sql is very basic.

INSERT INTO comment_flags VALUE('$user_id', '$comment_id')
DELETE FROM comment_flags WHERE user_id = '$user_id' AND comment_id = '$comment_id'

By Truncating then Optimising the table. After this I play with the code I have and I find that after about 5 'Unflags', I get about 30-40bytes of overhead as shown in phpMyAdmin — Sean H Jenkins, Dec 08 '11 at 16:20
See http://stackoverflow.com/questions/634257/innodb-does-the-space-occupied-by-deleted-rows-get-re-used — hafichuk, Dec 08 '11 at 16:21

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

I could optimise the table after every DELETE statement to clean up the table. Is this seen as a good idea?

For 30-40 bytes? No.

How does everyone else deal with this?

By not bothering. The space should be re-used for any subsequent INSERTs, and this is a good thing.

edited May 23 '17 at 12:03

Community

1
1

answered Dec 08 '11 at 16:19

Lightness Races in Orbit

378,754
76
643
1,055

@SeanHJenkins Maybe not great, but correct. You'd probably want to `OPTIMIZE` the table after a lot of `DELETE`/`INSERT` happened. – Romain Dec 08 '11 at 16:24
Because I'd rather find a way to deal with the overheads than to 'not bother'. Otherwise it could have huge implications in the future. – Sean H Jenkins Dec 08 '11 at 16:25
At what point would a overhead become a problem? 1kb? 2kb? 500kb? – Sean H Jenkins Dec 08 '11 at 16:26
@SeanHJenkins: If you've already decided to ignore everybody's experienced advice, then why ask in the first place? It doesn't make my answer "not great" just because you don't like it. Your use of the term "overheads" is completely inaccurate anyway. Just let the DBMS do its job and quit worrying about a handful of bytes that you may not be filling with data for a few minutes. – Lightness Races in Orbit Dec 08 '11 at 16:28
I'm not ignoring it. I just believe that answering my question with 'dont bother' is not really a helpful answer. – Sean H Jenkins Dec 08 '11 at 16:30
@SeanHJenkins: I'm sorry you feel that way. It precisely and correctly answers the question. If I changed "by not bothering" to "people _don't_ 'deal with this'" (which means the exact same thing), would you be happier? – Lightness Races in Orbit Dec 08 '11 at 16:31
No, no. Simple mis-understanding. I've read through the link you left. Thanks – Sean H Jenkins Dec 08 '11 at 16:33

MySQL overheads, is my query wrong or should I Optimise the table?

1 Answers1