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.
mysql table sizes
Moderator: General Moderators
Re: mysql table sizes
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.
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
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: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.
Code: Select all
messages table
gameid int
to varchar(100)
from varchar(100)
subject varchar(100)
message text
dated dateedit: each game could easily have more than 5000 messages (they last a long time, up to 50 weeks).
Re: mysql table sizes
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
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.