Trees in SQL

The problem of how to handle trees in SQL has been talked about alot. The basic 3 ways are:

  • store the full path for each entry
  • store the parent for each node
  • use nested tree

Nested tree is good for read-many-write-less applications where the tree doesn't check over time ...

more ...

Analyzing complex queries

When you ask someone how to optimize SQL queries you will always get the answer

  • enable the slow query log
  • set the long-query-time to 1-2 seconds
  • enable the logging of query which aren't using an index
  • run EXPLAIN on the queries that are shown and optimize them with proper ...
more ...

typesafe objects in PHP

I always disliked the way PHP handles Objects. There is no way to assign a type to properties. Validators have to be glued against the fields externally and you can't just generate a Object-Description (like WSDL) from a object either.

Usually you have DataObjects like:

/* a plain-old-php-object */

class Employee ...
more ...

building sigar

SIGAR is a cross-platform API to gather system-specific performance-data. It is developed by Hyperic and released under the GPL. We use it at MySQL as the backend for our monitoring agent.

Sadly SIGAR requires ant for building as it is meant to be used with a JNI-wrapper in Java applications ...

more ...

flv streaming

High performance flv-streaming with lighttpd is possible since lighttpd 1.4.11.

With lighty you can easily handle 10000 parallel downloads of your movies including protection against hot-linking with mod_secdownload. This is basicly all you need to build the free video.google.com for yourself.

Just add this you your ...

more ...

moving around

jan.kneschke.de is back online again. The old server got old and rusty and it has taken while to get everything setup again.

The most important articles are online again:

The old photo-storage is disabled, but a few new pics got uploaded to ...

more ...

php shell

The more I work with other languages like python and ruby I like their way how they work on problems. While PHP is very forgiving on errors, it is weak on the debugging side. It was missing a simple to use interactive shell for years. Python and Ruby have their ...

more ...

pxtools 0.0.20 released

Download

Known Bugs

The 64bit build of pxtools-0.0.20 will create wrong table names as seen in the comments below. As workaround use the 32bit version:

$ apt-get install pxtools:i386
more ...

groupwise max

... or "How to solve the same problem in 10 different ways".

One of the common problems to solve in SQL is "Get row with the group-wise maximum". Getting just the maximum for the group is simple, getting the full row which is belonging to the maximum is the interesting step ...

more ...

ORDER BY RAND()

If you read the MySQL manual you might have seen the ORDER BY RAND() to randomize the the rows and using the LIMIT 1 to just take one of the rows.

SELECT name
  FROM random
 ORDER BY RAND()
 LIMIT 1;

This example works fine and is fast if you only ...

more ...