Hello All,

I have been a MYSQL fan since many years, and used it in many of my applications, i think it is really good.

Now, i have a chance to select between database while making this new web application which is fairly large (of course mysql is my selected). but I have a few queries and would appreciate if any of you good guys can help me :-)

By fairly large application I mean:
1. More than 300-400 tables
2. About 200-250 concurrent connections
3. Some tables will have rows as low as 1 and high as 3-4 million, this will increase as time passes when application is used

I have following concerns, and would appreciate any guidance:

1. Due to the amount of data and many concurrent connections, I want to create another table that has history of all db transactions for every table. i.e. I want to keep history of any records changed. Any update/delete query fired within MYSQL should automatically be recorded in another table. So that if accidentally I record is deleted/updated which was not suppose to be. Then I can still get it. Or If I want to show log of changes for a particular row, I can query that table and get log of changes done.

2. Again due to fairly large amount of db (which is going to b used on web application) I wanted to find an effective way to do backup's or replicate while the db is live. I cannot afford to shut down mysql server to do backups.

Some simple questions which very well read users can reply.

1. How many concurrent connections are allowed in mysql?

2. How many max records can a table have?

3. Any tips to optimize mysql database?

Thanks in advance for reading so far :-)

Please share your ideas, views and knowledge on this.

Best Regards,
Sawan

This seems like a lot of tables. Can we ask what the database holds and why you need so many tables.

Most people would just have a single table but with an active flag and a timestamp for each record. When you alter a record you insert a new record and just change the old record to inactive.

Hi Mike,

Thanks for you reply. Please find my reply below:

This seems like a lot of tables. Can we ask what the database holds and why you need so many tables.
............It is a POS + ecommerce + backoffice system of a retail chain of stores.

Most people would just have a single table but with an active flag and a timestamp for each record. When you alter a record you insert a new record and just change the old record to inactive.
............. I think that might only make the table huge, and would create more records. I need something that can log every query I make to the db or a mysql trigger that can do so. That way if I have to look up in history and find out how the record was edited, I should be able to lookup the logs easily.

Regards,
Sawan