On Wed, Oct 17, 2012 at 10:53 PM, Bastien Koert <phpster@xxxxxxxxx> wrote: > Hi All, > > Below is a situation I ran into recently. To me, the decision made > shows how to not solve a problem but merely compound the issue. > > Scenario: There is an app currently running on a highly loaded 4.11 > MySQL db. Customers are complaining about the slow performance. > > The app has 3 important features (configurable forms created in > userland, confgurable reporting engine and the ability to add/create > an unlimited number of fields). The app does this by having a 200 > field main table running in the normal fashion and a smaller table > that holds the userland created fields that essentially runs > vertically. It also supports MSSQL as well > > Under MySQL 4.11 the join between the two tables is problematic in > that queries must pivot the small table to make it match the large > table. > > There are also a number of MySQL 5+ servers available. > > The solutions: > There are three solutions, one mine and two from a manager. > > Management Solution 1. > > Create another 500 column wide table and transfer the data from the > smaller vertical table to the large new table > > Issues: > 1. This kills the add unlimited fields feature (which makes a lot of > sales happen) but improves performance > 2. The DBA in me screams about a 500 column table as a really bad idea > since 99% of the fields will be null > 3. Requires another code base (already too many) > > Management Solution 2. > > Create 50 additional fields on the main 200 field table to move the > most used fields from the vertical table and improve performance > > Issues: > 1. Doesn't solve the additional vertical fields problem, just delays > the performance slow down for a while > 2. Required another code base ( the DEV in me hates this, there are > too many already) > 3. Only solves the problem for ONE client (the PM in me hates this > since its about a months work to analyze and code) > 4. Creates a non standard app database (the DBA in me hates this) > > My Solution: > > Move the DB to MySQL 5, re-code the report engine to create views > against those tables without altering the db structure > > Issues: > 1. slightly more complex code > 2. time to migrate and test system > > The Pros: > 1. The solution can be used across both MySQL and MSSQL with some > minor syntactic sugar > 2. Standard DB for app > 3. Simpler to code and doesn't require major analysis to do > 4. Keeps everything in one codebase > > > Now its obvious which way I lean, but mgmt decided on solution 2. > > I'd like some comments to see what you guys think. > > -- > > Bastien > > Cat, the other other white meat > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > As soon as you started talking about the structure, my first thought was "views", so yeah, that's where'd I'd have gone. Then the subversive in me whispered "MongoDB"... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
