WishList: Own a mySQL “superduper” extension on my PHP production server

I hope much from PHP 5 and MySQL 5 when developing a application which provide summary reports. The constraint is coming from the requirement. OK, I make the code with full PHP 5 and MySQL 5 features.

I have 3 master tables which have one to many relationship. I am looking for feature that could combine the three table, and I do INNER JOIN on those 3 tables. I should retype this as variable in PHP code, which is hard to read and I should send several bytes to MySQL Server.

Description of SQL code that I build (this is fake, i am trying to give a case):

SELECT A.User, B.Thread, B.ThreadName, C.Topic FROM myUsers AS a 
 INNER JOIN myThread as B ON a.id_user = b.id_user 
  INNER JOIN myTopic AS C ON b.id_thread = c.id_thread;

Maybe you ask, why I don’t use asterisk near SELECT (example SELECT * FROM)? because I don’t need all fields and I want perfomance!. For queries like this the asterisk giving big impact perfomance output. Can you Imagine how long the query is? and when needed the WHERE clause also be used on the queries.

Oh yeah, you might said . Just use variables in PHP!. Heck, the matter is not that simple, I want 75% process is held on mySQL server because I need computing and processing to currency with SUM, MAX, MIN, and so on so forth. 

The purpose is PHP + HTML become the presentation layer. I would use stored procedure when knowing have to create a summary, but I was develop too far which give me effort and time to edit all mySQL command into mySQLi. I should consider to use mySQLi extension for next projects. The disadvantage to use mySQLi is run slow for some machine.

According to PHP manual, mySQLi could support for mySQL server version 4.1 and above. Which help me to use Store Procedure. I remember that mySQL have connector, because I am using it in .NET too. I visit mySQL developer website and I found no connector for mySQL 5.0.45. The stories goes on and I am looking the best way to make the process faster.

I really want to use VIEW feature in mySQL to shortening the long code like that, so I just call

SELECT * FROM myViewTable

and giving the same result as the previous query. Well, the code is finished and I use it for fun in mysql client console. I read that VIEW have limitation, so I don’t use it.

Now, the code is working good but often make the CPU work hard, it utilize around 84% – 100% only for mySQL. In some case, maximum timeout is arise. This is happen because the bootleneck between PHP engine and mySQL server, so user must wait a moment when generating report in real time. Increasing maximum time for processing and memory available for php engine is just for temporarily solution.

Is there any mySQL “superduper” extension?. One conclusion has been made:The production server will always need continuously patched with new version of connector to make the availability of the web application reached.

Another question is came up to the case, “Are LAMP ready for Enterprise and OLAP application?”.