CodeIgniter has a very nice feature called a Profiler, which displays information at the foot of a page, information such as GET, Memory, POST data. It also shows database queries and how long they took to run which I find very useful.
I have recently taken over a legacy PHP application and it would help a lot to see what tables in the database are being queried by PHP pages. Is there a library for raw PHP applications that could produce something similar to CodeIgniter’s Profiler? I am already using xdebug, I am only interested in profiling database queries.
6
CI’s profiler is a nifty little class that assembles data from CI’s database class and CI’s controller class and gives you a pretty thorough view of what’s going on. Unfortunately it’s not going to work in isolation, it’s tightly coupled with the rest of the framework, and afaik there isn’t a simple alternative that you could plug in into a legacy application.
Since you are mainly interested in profiling database queries and assuming you are using MySQL, you should check out the General Query Log:
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)
From MySQL 5.1.6 you can store the log in a MySQL table, thus the logs are extremely easy to query and present.
PostgreSQL has a similar mechanism that’s also quite easy to setup (PostgreSQL manual > 18.8. Error Reporting and Logging). If you are using PostgreSQL, you should check out pgFouine, a log analyzer written in PHP. I’d imagine every other major RDBMS provides similar logging mechanisms.
The query log will give you all the information you’ll need for your database queries, and it’s quite easy to hack together a simple script to prettify the reports. If you’re interested in profiling HTTP requests as well, then you should look at Apache’s logs (or your favourite web server’s logs). For log analyzers, you should check out AWStats, apache-scalp and PHPIDS.