0

I'm building a website to monitor a bunch of IOT devices. E.g. Online/Offline status of each devices and some device specific information it may report back, IP address, Temperature etc this will vary. FYI These devices report back to my site via a processor/computer that poles these devices and then reports back (a maximum of 255 devices but in most cases between 10 - 100 devices).

To date, my approach had been that for each processor I would create a new table with just that processors devices would reside within. However in discussions with a colleague he suggested this might not be the best way to go, as it isn't particularly efficient and could be problematic later on e.g. if you wanted to add another column later on, having to add this to possible 50+ different processor tables etc.

Instead because all these tables would have the same structure e.g. identical amount of columns etc just the amount of devices e.g. rows would vary, would one big table with all these rows was a better way to go?

I know that in MySQL terms "scanning" is an expensive operation, and with one big table I would argue there would be more scanning as I would have to filter as I would have to take one big data set each time, and filter it down into a view, e.g. Processor or location against 5000+ rows vs lots of smaller tables of 100 rows. Also I would argue the data in this table would be written to allot e.g. each time a device goes offline the offline flag is updated, so I'm not sure if that makes it more suitable to a single table vs one large table.

Appropriate there's many different ways of approaching this, I just don't want to go down one rabbit hole and regret it later on. Front end will be PHP if that counts for anything.

*Originally posted on stack overflow put reposted here as the question warranted “opinion based answers”.

3
  • 1
    I know that in MySQL terms "scanning" is an expensive operation - That's why you have indexes! Go with one table - with 50M records, no bother, 500M, not a problem. Consider aggregations - much easier within one table! It's a no-brainer! Apr 14, 2020 at 9:41
  • Welcome to DBA.SE. Your question might probably be closed here for the same reason. There are just too many if ...then ... else .... situations that could arise or be a reason for some person to recommend a solution vs. another solution. You might also want to have a look at Chapter 22 Partitioning in addition to Vérace's recommendations.
    – John K. N.
    Apr 14, 2020 at 9:53
  • Check out this site if you want to consider partitioning - read long and hard there and elsewhere. For the moment, if I were you I would use 1 table and then see how that goes - no need to make things complex before it's absolutely necessary! Apr 14, 2020 at 11:45

1 Answer 1

1

one big table with all these rows was a better way to go?

Yes, absolutely

With proper table structures and indexing, MySQL will comfortably handle millions of rows in any table. The "Table-per-?" model is never a Good Idea and invariably comes back to haunt you (usually when you need to summarise across all those tables).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.