My database has 2 main tables:
Every day there are 10000 rows added to table1 and 1000 rows added to table2.
What is the recommendation for number of rows per table (for both tables, notice my second table has rows at size of 3.3kb)?
I want to optimize performance when updating a row or running a query.
Also, what is the recommended way to backup my tables?
As far as I have read there is no "recommended size" of a table. Yes, smaller tables have faster query response times. The best way to do things is that if one or more tupple(s) are not going to be required in the near future you move them to a backup table and have a trigger execute in case a tupple in not found in the main table to search for them in the backup table.
Think of it as a book's index, the smaller the index the faster it is to process, however there's no recommended size.
Database software is designed to store at the least millions of records without a problem as long as your h/w supports it.
Imo, move all the non-essentuial records (records not accessed frequently) into separate tables based on time of access. This way you can an active table and an archive table. The active table is thus much smaller and faster to access.
I hope I resolved your question.
As far as table backup goes, use the compression command in InnoDB to compress and then backup your table(s).
www.innodb.com/doc/hot_backup/manual.html#backup
If you are not using transactions while storing or updating tables, switch table type to MyISAM its quite faster than Innodb with much less overhead.
Add Indexes to frequently accessed columns
You can consider partitions. partition the table on access date or other imp key.
for backups you can always dump the database. synchronization is other option.
