my db growing too fast!!! help

Anon

i am trying to back up my mysql db and i m not sure whether to do it monthly or weekly...how many rows (avg) should a table hold for the db to be read at acceptable times?

Also, if lets say i was to do this monthly, i am thinking instead of putting my data in an sql file to put them in another table because i need to be able to query and display previous months' data. Is this the way to do this or is there a better way to seperate my tables and still be sql accessible?

PS: no need to mention that i m new to db maintenance, right?

Anon

There are too many variables to give you an answer with what you\'ve posted (server size, load, database design, code efficiency, etc).

But with a good database design and proper indexes for your queries, mysql should be able to handle some pretty big tables with acceptable response times.

How many tables, rows, and db hits are you dealing with?

Anon

i have over 15 tables...but some have 150,000 rows and increasing fast...those ones i ll have to do something about...i thought of putting them in txt files using mysqldump...is there an automated way to do that? ie: if end of month transfer everything and empty table?

on the other hand there are some tables that i ll need to query even next month....so i thought to just create a new table each month

does mysql have any automated functions to do all these or will i have to do it in my website php pages? thanks

PS: to be more detailed: i m running a search engine and i have a table that records all searches made. currently i have 4000 searches per day and that number is expected to increase over the next few days...now that table will have 120,000 rows in a month...what do u suggest? (cant just put in sql file cause i need to pull stats)

Anon

Hi,

120,000 records a month is really nothing to worry about. I would just create a cron job that back's up the database every night. and then compresses it with a time/date stamp so you have some idea where it came from.

Don’t stress this is nothing.. i had some databases where i was getting 120,000 a day 😛

Chris