The products table is getting too cluttered. Consider JSON encode

[NadalKumar]

The configoption columns 1 - 24 are a bit of a mess and would certainly serve well as a consolidated json encoded single column. I tested the method and it works well. Of course it would require core code changes, but isn't that all part of delivering efficiency!

Example

configoptions => json_encode(['config1' => 'value1', 'config2' => 'value2']);

therefore eliminating 23 columns and provide a simpler method for implementing future options by simply adding the form fields only and no additional DB column. The same is true for tblpricing.

[Kian]

The point of having columns is that they provide the best performances and they're easier to handle. Of course you can json_encode anything you want but first you should ask yourself if it is worth it. For example on paper you could json_encode all columns of tblclients table but performance-wise json the need to decode anything on every page load has a negative impact on loading time. When it comes to filtering, ordering, joining (...) records it's even worse since you can't make use of any index. Moreover you can't really perform any join. For example a join between 5 tables moves from a single query to 5 queries with lot of PHP involved just to re-group data. This way a very light operation becomes exponentially heavy duty and a nonsensical nightmare to write.

In conclusion having lot of columns is perfectly fine. That's the purpose of databases. Your suggestion has just downsides and no positive aspects.

Edited June 8, 2019 by Kian

[NadalKumar]

On 6/8/2019 at 7:18 AM, Kian said:

Of course you can json_encode anything you want but first you should ask yourself if it is worth it. For example on paper you could json_encode all columns of tblclients table but performance-wise json the need to decode anything on every page load has a negative impact on loading time.

Yes it is worth it.

Obviously you don't know how to use JSON. The array is processed once and stored in a reusable variable so there is no excess resource. hundreds of table columns will cause resource drain and lag. It's the key reason WordPress and numerous top website systems use JSON to process data which would otherwise require excessive tables.

[Kian]

Few instants ago I completed a test with 50k records.

On the left there's a single column that contains 16 parameters all encoded as json. Table size is 16 MiB. In fact json encoding adds 83 unnecessary extra characters for markup on each row. On the right there are the same parameters on 16 columns. Total size is 6.7 MiB (58% less space). I selected all records from both tables 5 times and compared loading times. The results are the following:

• Json encoding - Average 0.6331 seconds
  • 0.6953 seconds
  • 0.7577 seconds
  • 0.5561 seconds
  • 0.5419 seconds
  • 0.6146 seconds
• Non-json - Average 0.3226 seconds
  • 0.3393 seconds
  • 0.3058 seconds
  • 0.3432 seconds
  • 0.3120 seconds
  • 0.3110 seconds

Interacting with json-encoded data is 96% slower. If I add a WHERE condition results are even worse with json 127% slower (0.0025 vs 0.0011) and I can't even get an exact match or use more than one condition without the help of PHP that increases even further loading times. We're just scratching the surface. Let's say that I need to JOIN this table with another one. Normally I would run this query getting what I want in few milliseconds:

SELECT t1.Surname, t2.firstname FROM _test2 AS t1 LEFT JOIN tblclients AS t2 ON t1.id = t2.id

With json such an easy task requires me to code a very long and inefficient PHP script. Same story repeats with any other MySQL statement (date, avg, max, min, order by, having, group by, ifnull, concat, date_add...) that cannot be used when your data is encoded. I didn't test I/O but it's very easy to imagine the outcome:

// I want to get firstname

// Column-way
SELECT firstname FROM sometable
// returns Harrison

// Json-way
SELECT data FROM sometable
// returns {"GivenName":"Harrison","Surname":"Newling","CompanyName":"","VatNumber":"","VIESAddress":"","Zone":"","EmailAddress":"HarrisonNewling@jourrapide.com","StreetAddress":"38 Spencer Street","City":"FRASER ISLAND","State":"QLD","ZipCode":"4581","Country":"AU","TelephoneCountryCode":61,"TelephoneNumber":"(07) 5374 4443"}

What is more efficient? Getting 8 characters or 317 and then the 8 characters I really wanted? If you want to test it yourself I can provide you both tables. If you don't trust me you can get more details here, here, here, here and here.

Don't get me wrong. it's not all black and white. It depends. The vast majority of times in WHMCS you want columns instead of encoded data. I'm not saying that using json is always wrong but in this specific case (tblproducts) it's a bad idea. We're not talking about WP comments that simply need to appear as they are. MySQL functions are crucial. I'm thinking about tens of hook points where on a daily basis hundreds of people need to join tblclients with tblorders, tblinvoices, tblhosting, tbldomains and tblproducts for various reasons and we can do everything quickly with a single query and all filters we want. Json would make things more complicated bringing no one single positive aspect.

Edited June 16, 2019 by Kian