phpMyAdmin mitigates VIEW problem with MySQL 5.0
Besides many other bugs, the first beta release of phpMyAdmin 2.8.0 partly fixes a dangerous behaviour that could lock a database with a complex VIEW, as mentioned in one of my previous posts.
The problem was that phpMyAdmin did an explicit row count on VIEWs on many occasions, for example every time a new schema was selected. For complex VIEWs (with JOINs on huge tables) this row count could take a really long time and finally time out the browser, lock involved tables and build up too many connections on a busy server.
How phpMyAdmin now deals with VIEWs
In the discussion on my bug report the phpMyAdmin developers decided to not drop the row count for VIEWs completely nor to make this behaviour configurable.
Instead they decided to use the existing configuration setting MaxExactCount that's already used for table engines without an exact row count in their SHOW TABLE STATUS output (e.g. InnoDB). Only if the approximate row count for such tables is less than the configured setting phpMyAdmin does an exact count with a SELECT COUNT(*) FROM table_name.
For VIEWs phpMyAdmin now uses a SELECT 1 FROM view_name LIMIT MaxExactCount and counts the resulting rows instead of doing a SELECT COUNT(*) FROM view_name. If the result equals MaxExactCount phpMyAdmin displays a note telling us that the count shows only a lower boundary for the effective number of rows in the VIEW. This should fix the problem for most of the complex VIEWs, as MySQL now only has to compute a limited set of rows.
Thanks to open source
This issue once more shows the advantages of efficiently lead open source projects: Firstly I was able to create my own little hotfix, secondly only hours after I posted my bug report, it was accepted by the developers, then there was an open discussion on how to solve the issue and finally there was a fix in the next release already, everything within a few weeks.
Final solution?
The fix solved my original problem, so my thanks go to the phpMyAdmin delevopers for how they deal with such issues. But I'm still not quite sure if any row count on a VIEW by default is the right approach, just to gather some statistics.
VIEWs are dynamically programmable database objects after all, such as stored procedures and functions are. Nobody would call a procedure by default just to display the number of result sets and rows it returns in an overview page.
You could think of a VIEW that even with a LIMIT clause takes a long time to execute, which could be perfectly acceptable if it's only used by some reporting tools that usually are executed offline. Furthermore a VIEW effectively could have side effects on your data (even if this is probably considered bad style). Just read Roland Bouman's article on how to achieve sort of TRIGGERs on a SELECT.
So while this fix is maybe not the final solution, it certainly mitigates the problem with VIEWs while maintaining the original functionality of phpMyAdmin.
