Page 1 of 1
mysql table sizes
Posted: Thu Aug 21, 2008 11:00 am
by Chalks
I'm in the process of building something that could potentially be adding anywhere from 500 to 5000 text messages a week to a table. At what point do mysql tables really start slowing down, and is there any real limit as to how much I can store in one? I mean, if I have 100,000 records in a table and I need to pull out 50 records, is that going to be significantly slower than having a table of 10,000 records and pulling out 50?
I guess my question is really, how often should I purge records?
Incidentally, server size and bandwidth is not an issue. I'm solely concerned about speed.
Re: mysql table sizes
Posted: Thu Aug 21, 2008 11:59 am
by coderdan
You will have no problem if you have a good index and queries that can take advantage of that. I have a table with 10M text records on a budget server with a lot of queries and inserts and it works without problems.
You should purge records when it doesn't make business sense to keep it. Note that if you delete/insert records very often than the perfomance might degrade a bit. You will need top OPTIMIZE the table and that takes time and locks the table.
Re: mysql table sizes
Posted: Thu Aug 21, 2008 12:24 pm
by Chalks
coderdan wrote:You will have no problem if you have a good index and queries that can take advantage of that. I have a table with 10M text records on a budget server with a lot of queries and inserts and it works without problems.
You should purge records when it doesn't make business sense to keep it. Note that if you delete/insert records very often than the perfomance might degrade a bit. You will need top OPTIMIZE the table and that takes time and locks the table.
Glad to hear that. I'm not really sure how to optimize this however, or what you mean by "top OPTIMIZE". The table I'm most concerned about is this one:
Code: Select all
messages table
gameid int
to varchar(100)
from varchar(100)
subject varchar(100)
message text
dated date
there will probably only be 10 or so games active at a time, but I want to keep records of all the messages from older games. So, messages from active games should be near the top of the table? I don't know what an optimized mysql table looks like.
edit: each game could easily have more than 5000 messages (they last a long time, up to 50 weeks).
Re: mysql table sizes
Posted: Thu Aug 21, 2008 12:34 pm
by coderdan
That was a typo. You need to OPTIMIZE:
http://dev.mysql.com/doc/refman/5.0/en/ ... table.html
The most important thing is to make sure each query is using an index. You can check this with
http://dev.mysql.com/doc/refman/5.0/en/explain.html
Re: mysql table sizes
Posted: Fri Aug 22, 2008 9:21 pm
by califdon
Just thought I'd chime in here to say that I agree with coderdan 100%. MySQL is a robust database engine and if your queries are using indexes, they will be fast.