Profiling with MySQL

categories Computer, MySQL, Tutorials    

Whenever you encounter a query slower than you expected, and you’re sure you’ve done everything in your power to ensure your tables are properly optimized and indexed, you might want to profile it. MySQL has an inbuild profiler, which allows you to see very detailed for what part of the query how much time has been spend.

To use it, follow these steps:

(1) Activate the profiler.

Since the profiler is deactivated by default we first have to start it.

SET profiling = 1;

(2) Execute your query.

(3) Find out the query id for profiling.


It will return you something like this:

    Query_ID |  Duration | Query
      ...    | ...       | ...    
       29    | 0.0006200 | SHOW STATUS
       30    | 0.3600000 | SELECT (your query here)
      ...    | ...       | ...

Now you know the query id is 30.

(4) Profile the query.

SHOW PROFILE FOR QUERY 30;   // example 

This will return you the details, which might look like this:

    Status                          | Duration
    starting                        | 0.000010
    checking query cache for query  | 0.000078
    Opening tables                  | 0.000051
    System lock                     | 0.000003
    Table lock                      | 0.000008
    init                            | 0.000036
    optimizing                      | 0.000020
    statistics                      | 0.000013
    preparing                       | 0.000015
    Creating tmp table              | 0.000028
    executing                       | 0.000602
    Copying to tmp table            | 0.000176
    Sorting result                  | 0.000043
    Sending data                    | 0.080032
    end                             | 0.000004
    removing tmp table              | 0.000024
    end                             | 0.000006
    query end                       | 0.000003
    freeing items                   | 0.000148
    removing tmp table              | 0.000019
    closing tables                  | 0.000005
    logging slow query              | 0.000003
    cleaning up                     | 0.000004

In this example, most of the time was actually spend sending the data from the server back to the client. Maybe try narrowing down the amount of data you get from the query. Maybe raise your query cache. Maybe do something different – its entirely up to your database, server and needs what to make of this result. But now you know where to look!


Firebug: logging features

categories Computer, Tutorials    

Many developers think of Firebug as the mother of all web tools. There’s a reason for it, because Firebug contains sophisticated tools to do nearly everything you need being a web developer, starting with little css experiments, debugging complex jscript-applications, profiling of web pages and many more.

Not commonly known is how to create your own logs using JScript.

For example, …

console.log('Hello World!');

prints exactly this in your Firebug console.

You can refine this, since most of you distinguish informations, warnings, errors and debug output.


Debugging variables can be helped, too.

var banana = 1.234;
var orange = "Test";
console.log("my %a has the value %d", orange, banana);

If you have the need to group log output, just do it like this:

    console.debug('What did I want to know?');

Even more features, p.e. timing values, stacks and so on, are documented here.

By the way:
Most of these features have already found their way into the debugger used by Google Chrome!


DOS: creative WAIT command

categories Computer, Tutorials    

Maybe you’ve encountered this before: there is no direct way to add a simple command like WAIT x seconds in DOS. While forging an easy solution for a simple task, I’ve found a nice, create way of solving this:

PING -n 1 -w 25000 >nul

Yes, indeed. The PING-command is used to wait.

A few additional explanations:

Of course, usually I try to avoid using a WAIT-operation in scripts at all, and this ain’t a very exact technique. It simply does the job for most tasks where a WAIT-command might be required.


visualizing sorting algorythms

categories Fundstücke, Tutorials    

The development of sorting algorythms may look for non developers a little bit like black magic, but it should be basic knowledge for a coder.

A nice, coffescript-based page visualizing that sometimes very dry topic can be found here:



Unittests with PHPUnit

categories Computer, PHP, Tutorials    

A little private encounter with the community of an open source project opened my eyes, how good and easy life as a coder is if you’re working with unit tests. Not everybody uses them, but everybody should. For php programming the ideal software for this is PHPUnit.

The software can be obtained from GitHub verteilt, is well documented, widely used and very popular. It is also in the PEAR project and can be installed using the PEAR installer (it has some dependencies for additional features).

To demonstrate the basic procedural manner I’ll use a simple example. Imagine… somewhere within a php project there is a function, which returns easter sunday given any year. To make things easy, we’ll name it easter($year). Such a function could be used for determining holidays and therefore could have a certain criticality. How can this be tested with PHPUnit?

Let’s delve into code:

require_once 'includes/functions.php';
class EasterTest extends PHPUnit_Framework_TestCase {
  public function testEasterOk() {
    $easter2011 = easter(2011);
    $this->assertEquals($easter2011, gmmktime(0,0,0,4,24,2011));
  public function testEasterFalse() {
    $easter2011 = easter(2011);
    $this->assertNotEquals($easter2011, gmmktime(0,0,0,12,24,2011));

First there is a link to the script containing the easter-function (here: functions.php). Then I generate a new class (“EasterTest”), which extends PHPUnit. In this class there are two basic tests:

Now to the boring theoretical part…
The solution isn’t the aim here – one glance at a calender should help you way better. The goal is, to compare the functions result with a defined result. I know for sure, that this years easter sunday is on 24.04. (as long as my calendar isn’t lying!) … und I know for sure, 24.12. ain’t somewhere near easter.

PHPUnit – like many other software products around unit testing – works with “assertions”. There is a library of simple functions for assertions in different constructs – for comparisms (like the here used assertEquals and assertNotEquals), arrays, bools and so on.

How much work is it?
Please decide for yourself. An easy test should be coded within less than five minutes. Not too difficult, ain’t it?

Back to work.

How can you now run those tests? Easiest way: use your shell (whatever, either Dos, Bash or anything else php runs on).

D:\htdocs\>phpunit EasterTest.php
PHPUnit 3.5.11 by Sebastian Bergmann.

Time: 0 seconds, Memory: 3.75Mb
OK (2 tests, 2 assertions)

PHPUnit can express itself using different output formats, for example TAP (“Test Anything Protocol”), JSON, XML and some more.

D:\htdocs\>phpunit --tap EasterTest.php
TAP version 13
ok 1 - EasterTest::testEasterOk
ok 2 - EasterTest::testEasterFalse

Now… how does it look like if there is an error? To test this I slighly changed my first test and assumed, 23.04.2011 is easter sunday – in reality it is the saturday.

D:\htdocs\>phpunit --tap EasterTest.php
TAP version 13
not ok 1 - Failure: EasterTest::testEasterOk
  message: 'Failed asserting that <integer:1303516800> matches expected <integer:1303603200>.'
  severity: fail
    got: 1303516800
    expected: 1303603200
ok 2 - EasterTest::testEasterFalse

If I return coding on the easter-function in the far future, then I still have my unit tests and can run them whenever I like (or whenever I need!). If the tests then return errors, either my tests are wrong or my function is (guess what is more likely). And this is what this is all about. Unit testing makes your code stronger and additions much easier.

And…if you encounter bugs… just write a test first and then fix it!

Next Page →