One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction.

Consider we're running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there are also bunch of fields which sorting can be performed on such as seller, price, date added etc.

Such configuration often causes serious challenge choosing proper index configuration as it is hard to add all combinations of restrictions and order by to be fully indexed.

An extra problem comes from the fact MySQL prefers when it is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for further restrictions. Here is example:

PLAIN TEXT SQL:

1. CREATE TABLE …

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:

• Optimizing ORDER BY RAND()
• Groupwise MAX()

The old photo-storage is disabled, but a few new pics got uploaded to flickr, e.g. from my trip to Argentina:

• http://www.flickr.com/photos/jankneschke/sets/72157594494461199/

Some of the common conceptions of data storage seem to have been blown out of the water.

Two things I found interesting:

In their study they found that there was no correlation between disk failure rates and utilization, environmental conditions such as temperature, or age. This means that high disk utilization or age of the disk have no significant impact on the probability that it will fail.

...

They observed that older disks had a much lower failure rates then newer disks, where the newer disks in general were less expensive.

Which makes me think that buying used HDDs off Craigslist might not be a bad idea.

One could buy cheap 15k RPM low latency disks from a few years ago and forget about the storage capacity in exchange for FAST seek time.

Of course it depends on how …

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. Real men use C, so we need something else for building: cmake.

cmake

cmake is a cross-platform, autotools replacement. It works on Unix and win32 and generates native Makefiles. On Unix it is make, on win32 it is nmake, on macosx uses the XCode framework.

That keeps even the build portable. You can get a binary from …

Download

• pxtools 0.0.20

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

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 ipython and iruby shell which give you a direct way to interact with the objects. No need to write a script and execute it afterwards.

Features

• tab-completion (if readline() support is compiled into php)
• handles FATAL errors (like calling a undefined function)
• inline help
• autoload() is enabled by default
• works on all php platforms (shell wrappers for Unix and Windows)

Requirements

• PHP 5 or later
• ext/tokenizer has to be enabled (this is the default)
• ext/pcre has to enabled (this is the …

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 lighttpd.conf and restart the server:

server.modules = ( ..., "mod_flv_streaming", ... )

flv-streaming.extensions = ( ".flv" )

Players

mod_flv_streaming expects you to fetch the flv-file with a GET parameter if you want to do a seek into the file.

GET /movie.flv?start=23 HTTP/1.1
Host: ...

• Fabian Topfstedt has done this for …

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:

• Optimizing ORDER BY RAND()
• Groupwise MAX()

The old photo-storage is disabled, but a few new pics got uploaded to flickr, e.g. from my trip to Argentina:

• http://www.flickr.com/photos/jankneschke/sets/72157594494461199/

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. Real men use C, so we need something else for building: cmake.

cmake

cmake is a cross-platform, autotools replacement. It works on Unix and win32 and generates native Makefiles. On Unix it is make, on win32 it is nmake, on macosx uses the XCode framework.

That keeps even the build portable. You can get a binary from …

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:

• Optimizing ORDER BY RAND()
• Groupwise MAX()

The old photo-storage is disabled, but a few new pics got uploaded to flickr, e.g. from my trip to Argentina:

• http://www.flickr.com/photos/jankneschke/sets/72157594494461199/