Jump to content

  • Log In with Google      Sign In   
  • Create Account


#ActualSteve_Segreto

Posted 25 May 2013 - 03:55 AM

I'll try to answer your question:

 

 ​Is it more efficient to have a table with 600+ columns and 100,000+ rows, or 100,000+ tables with a average of 100 rows each? How much weight does a empty entry add to the database? Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

 

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.

 

Empty entries should not add much weight to the database, but you should really wonder why you need them, are you simply trying to keep the dimensions of two tables similar in some way?

 

MySQL most likely uses some sort of logarithmic search algorithm to find entries (i.e. a binary search).

 

This search is extremely fast even for HUGE numbers. For instance a computer can find one name in a phonebook with 1,000,000 names in just 20 comparisons. So you shouldn't worry too much about the number of search queries you need to make. But if you insist on worrying, see if it's possible for you to batch them all together and make only one large query call through the API to reach MySQL and ask it to batch search many rows at once, because the time to issue the call and connect to MySQL might be slower than the time to search.

 

It's nice you are making a game for your wife. I agree with Sky Warden, you should share your usage of this database with us, because it sounds like possible overkill if you have 100,000 distinct tables.


#2Steve_Segreto

Posted 25 May 2013 - 03:41 AM

I'll try to answer your question:

 

 ​Is it more efficient to have a table with 600+ columns and 100,000+ rows, or 100,000+ tables with a average of 100 rows each? How much weight does a empty entry add to the database? Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

 

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.

 

Empty entries should not add much weight to the database, but you should really wonder why you need them, are you simply trying to keep the dimensions of two tables similar in some way?

 

MySQL most likely uses some sort of logarithmic search algorithm to find entries (i.e. a binary search).

 

This search is extremely fast even for HUGE numbers. For instance a computer can find one name in a phonebook with 1,000,000 names in less than 20 iterations. So you shouldn't worry too much about the number of search queries you need to make. But if you insist on worrying, see if it's possible for you to batch them all together and make only one large query call through the API to reach MySQL and ask it to batch search many rows at once, because the time to issue the call and connect to MySQL might be slower than the time to search.

 

It's nice you are making a game for your wife. I agree with Sky Warden, you should share your usage of this database with us, because it sounds like possible overkill if you have 100,000 distinct tables.


#1Steve_Segreto

Posted 25 May 2013 - 03:41 AM

I'll try to answer your question:

 

 ​Is it more efficient to have a table with 600+ columns and 100,000+ rows, or 100,000+ tables with a average of 100 rows each? How much weight does a empty entry add to the database? Is there a easy way to future proof the minimalist number of table concept without it eventually getting completely unmanageable?

 

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.

 

Empty entries should not add much weight to the database, but you should really wonder why need them, are you simply trying to keep the dimensions of two tables similar in some way?

 

MySQL most likely uses some sort of logarithmic search algorithm to find entries (i.e. a binary search).

 

This search is extremely fast even for HUGE numbers. For instance a computer can find one name in a phonebook with 1,000,000 names in less than 20 iterations. So you shouldn't worry too much about the number of search queries you need to make. But if you insist on worrying, see if it's possible for you to batch them all together and make only one large query call through the API to reach MySQL and ask it to batch search many rows at once, because the time to issue the call and connect to MySQL might be slower than the time to search.

 

It's nice you are making a game for your wife. I agree with Sky Warden, you should share your usage of this database with us, because it sounds like possible overkill if you have 100,000 distinct tables.


PARTNERS