mysql table sizes

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
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

mysql table sizes

Post 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.
coderdan
Forum Newbie
Posts: 3
Joined: Sat Jan 12, 2008 1:16 am
Contact:

Re: mysql table sizes

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: mysql table sizes

Post 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).
coderdan
Forum Newbie
Posts: 3
Joined: Sat Jan 12, 2008 1:16 am
Contact:

Re: mysql table sizes

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql table sizes

Post 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.
Post Reply