Jump to content

  • Log In with Google      Sign In   
  • Create Account

#ActualMadhed

Posted 25 May 2013 - 04:35 AM

One large table is likely to be more efficient than 100,000 small tables simply because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table

 

I think this can't be generalised so easy. tbh, 100,000 tables is a ridiculous number, but so is 1. Once your business logic becomes slightly complex you will run into various performance issues related to write locking, cache utilisation, index updates, etc.

 

"because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table".

 

This can be viewed from a different angle: Relational databases are highly optimised in dealing with relations. If your data can be modeled in a relational manner you should go for it. As Sky Warden suggested, OP should look up data normalisation.

 

Edit: The fastest database query is the one that doesn't happen. If you know you are going to read a lot of data in the future, you can preload it into an easier accessible storage. You could, for example, load the current room data the user is in into memory and check from there instead of constantly checking the database.


#1Madhed

Posted 25 May 2013 - 04:29 AM

One large table is likely to be more efficient than 100,000 small tables simply because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table

 

I think this can't be generalised so easy. tbh, 100,000 tables is a ridiculous number, but so is 1. Once your business logic becomes slightly complex you will run into various performance issues related to write locking, cache utilisation, index updates, etc.

 

"because MySQL is a relational database and there could be significant overhead in chasing down primary and foreign keys for each access to a different table".

 

This can be viewed from a different angle: Relational databases are highly optimised in dealing with relations. If your data can be modeled in a relational manner you should go for it. As Sky Warden suggested, OP should look up data normalisation.


PARTNERS