recommended max records per table

General project discussion. NOT for help questions.
Post Reply
User avatar
rotezecke
Power Poster
Power Poster
Posts: 411
Joined: Fri Apr 18, 2008 9:34 pm
Location: Nimbin, Australia

recommended max records per table

Post by rotezecke »

I am looking into writing a module to record timesheets for employees (and process them via export to external software). Each workday would create a record, per employee. If I only record the actual days worked, I have about 200 records per employee, per year. A company of 50 employees would therefore have around 10,000 records per year. This data should be kept for at least 7 years => 70,000 records, unless I export/save the data in between. As the company may also grow, I should expect at least 100,000. Is this pushing CMSMS / mySQL to the limit? Alternative ideas also welcome. cheers, Mario
User avatar
Jo Morg
Dev Team Member
Dev Team Member
Posts: 1921
Joined: Mon Jan 29, 2007 4:47 pm

Re: recommended max records per table

Post by Jo Morg »

rotezecke wrote: Mon Nov 02, 2020 12:59 am Is this pushing CMSMS / mySQL to the limit?
Not even close to!

Although CMSMS provides an abstraction layer to access the database, it doesn't interfere with it at all and you can always access it directly if needed for some specific operations that the abstraction layer may be more of a hassle than a boon.
Regarding MySQL, and MariaDB for that matter, the limits are a bit more theoretical... on the order of the Terabytes!

A few resources from the docs on MySQL limitations:
http://dev.mysql.com/doc/refman/5.6/en/database-count-limit.html wrote:Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.
http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html wrote:There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.
http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html wrote:Every table (regardless of storage engine) has a maximum row size of 65,535 bytes.
So in practical terms, in a shared hosting environment, you'll hit server specific limits before you are close to hit any of those limits. And you'r not likely to get near them with say, 1 million records, provided there won't be weird BLOB fields.
For instance, LISE has been working with multiple instances with near 200k records in some installs, and although it is not the champion of efficiency (LISE uses a database model that is not the best one for this kind of uses, at least not on it's current form) it holds its own easily depending a bit on what is requested of it.
So you can rely on CMSMS and mysql for most practical uses, just make sure you have a good database table design yo start with when you plan your module.

A quick note:
http://nickduncan.co.za/maximum-mysql-database-size/ wrote:Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)
HTH.
"There are 10 types of people in this world, those who understand binary... and those who don't."
* by the way: English is NOT my native language (sorry for any mistakes...).
Code of Condut | CMSMS Docs | Help Support CMSMS
My developer Page on the Forge
GeekMoot 2015 in Ghent, Belgium: I was there!
GeekMoot 2016 in Leicester, UK: I was there!
DevMoot 2023 in Cynwyd, Wales: I was there!
User avatar
rotezecke
Power Poster
Power Poster
Posts: 411
Joined: Fri Apr 18, 2008 9:34 pm
Location: Nimbin, Australia

Re: recommended max records per table

Post by rotezecke »

Thanks again for yet another detailed response, JoMorg. At this point, you put more work into this module than I have. :)
While the scope of the project isn't fully clear yet, I feel more confident it will work regardless of what they throw at me.
Very good point about shared hosting, though. That's already a known bottleneck. Cheers, Mario
Post Reply

Return to “General Discussion”