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
