MySQL database design

Question

I'm setting up a MySQL database and I'm not sure of the best method to structure it:

I am setting up a system (PHP/MySQL based) where a few hundred people will be executing SELECT/UPDATE/SET/DELETE queries to a database (probably about 50 simultaneously). I imagine there are going to be a few thousand rows if they're all using the same database and table. I could split the data across a number of tables but then I would have to make sure they're all uniform AND I, as the administrator, will be running some SELECT DISTINCT queries via cron to update an administrative interface.

What's the best way to approach this? Can I have everybody sharing one database? one table? Will there be a problem when there are a few thousand rows? I imagine there is going to be a huge performance issue over time.

Any tips or suggestions are welcome!

I also need to prevent people from seeing/editing other people's records (rows, not real records!) I'm hoping I can address this via prepared statements. Is this viable? — DesignGoggles
– DesignGoggles, Commented Apr 30, 2012 at 18:01
If you can explain in detail exactly what you're trying to do, I will attempt to help you with this. Contact me via my homepage in the user profile. Good luck. — ionFish
– ionFish, Commented May 1, 2012 at 3:01
Essentially what's happening is... People are loading a snippet of code on their websites which updates my database. That same snippet of code then reads my database and returns a set of data to the webmaster. That's about as much detail as I can provide. — DesignGoggles
– DesignGoggles, Commented May 1, 2012 at 17:39
So you're providing an API-type service hosted by you that other people insert into their sites, and then you can read all of the data in one place from your server? Can you please sketch the types of tables you would require? (I have no idea what type of data you're talking here). — ionFish
– ionFish, Commented May 1, 2012 at 20:37

Noodles · Accepted Answer · 2012-05-02 04:59:43Z

3

MySQL/php can easily handle this as long as your server is powerful enough. MySQL loves RAM and will use as much as it can (within the limits you provide).

If you're going to have a lot of concurrent users then I would suggest looking at using innodb tables instead of MyISAM (the default in MySQL versions <5.5). Innodb locks individual rows when doing INSERT/UPDATE/DELETE etc, rather than locking the whole table like MyISAM does.

We use php/MySQL and would have 1000+ users on our site at the same time (our master db server does about 4k queries per second).

answered May 2, 2012 at 4:59

Noodles

9284 gold badges15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DesignGoggles Over a year ago

Thanks for the Innodb tip. I had forgotten MyISAM locks the entire table. This would have been an issue as the project scales up. Putting everything in one table seems to be the way to go. Regarding server power I've barely put a dent into it's capacity during testing so I should be fine there. Question answered, Thanks!

Collectives™ on Stack Overflow

MySQL database design

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest