I’ve just deployed my V3 blog engine to live. In doing so, the time per query jumped from <10 mSec per query to ~2.5s — which meant that the typical page response time for non-cached pages increased from an average 0.25s to nearly 3s. (Cached pages are loaded from a static copy, so the render time is typically under 0.2s. This was odd as the timing for V3 remained much the same as the V2 engine (< 10 mSec) for my development and test versions. After quick ‘binary chop’ through the code, looking at the micro-timing (I have debug routines to do this), I found that entire increase was down to a single MySQL query which I used in the initialisation of my extension to the mysqli class:
SELECT TABLE_NAME AS name FROM information_schema.tables WHERE TABLE_SCHEMA = '<DBname>' AND TABLE_NAME LIKE '<TablePrefix>%'
and this was taking 2.3 – 2.4 seconds to run, so I replaced this by a functional equivalent which ran in < 2 mSec:
SHOW TABLES LIKE '<TablePrefix>%'
I picked the former because I wanted to explicitly name the column in the result set. However, coding around with the second query only adds an extra line of code. For this extra line, my script time drops by 100x and the user response at the browser decreases by 10x. This all goes to show you can spend ages worrying about whether this particular way of coding (say select case vs. if esleif chains) which only changes runtimes by µSec, but if the runtime suddenly increases in a bizarre manner, then its usually some bizarre quirk that you hadn’t anticipated, and you should do a timing drill-down to work out why.