On Wed, Oct 17, 2012 at 10:53 PM, Bastien Koert phps...@gmail.com 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
