Optimising MySQL a query with packed keys

I’ve been learning more about MySQL lately and particularly optimising
SQL queries on large tables. Large, in this case, being at the moment
hundreds of thousands of rows but soon to be millions. One of the
problems I’ve had is that MySQL sometimes decides not to use an index
even when a handy one seems to have been created for it. The root of
this appears to be to that with B-tree indexes if there are a large number of records with similar looking values then the MySQL engine may decide that it’s just as much effort using the index as to search the whole table.

The answer appears to be adding PACK_KEYS = 1 to the end of a create
table, or running the SQL command ALTER TABLE MyTable PACK_KEYS = 1 once
the table has been created. In effect, this takes account of the
similarity of adjacent keys. In our case we have a large column of field
type bigint(21) where the starting digits of the index are timestamp
generated. So, at present, we end up with a few tens of thousand rows
all starting with 108xx. Enabling packed keys means not only that the
index is smaller as MySQL only needs to store the differences between
keys (plus an extra byte to keep track of where the similarity starts)
but also that the index is actually of some use i.e. doesn’t become a
large, flat structure.

One down side of using packed keys is that inserts are slower, but given
that the system we are building is inserting each row once and then (in
theory) never touching it again that’s a small price to pay. The other major drawback, however, is that packed keys only works on MyISAM tables at present and not InnoDB. This actually isn’t much use to me as the large inserts we occassionally have to do would end up with MyISAM locking the table for perhaps an hour or more.

Pack keys
reference in the MySQL manual

Restaurant review: Gili Gulu sushi restaurant, London

It’s hard not to know about Yo Sushi, the ‘famous’ conveyor belt sushi
restaurant, but what’s not so well known is that the idea is almost a
direct import from Japan where such restaurants are more common. Gili
Gulu is another along the same vein in central London just off the main
stretch between Leicester Square and Covent Garden.

One of the big things that you’ll notice about Gili Gulu compared to Yo
Sushi is the price: the set meals are more than enough for an early
evening snack and come in at £7.50 for either a large noodle dish and
three sushi’s or miso soup and 6 sushi dishes. Very good value and even
with a drink on top you can have a reasonable meal for around £10. The
buffet is more of a blow-out experience but for around £11.50 it’s all
you can eat, including any of the noodle dishes off the menu.

The interior is well presented but informal. The tables are ideal for
groups of four, yet the bulk of the seating is based around the conveyor
belt which snakes around the space. This is ideal for a couple or if
you’re on your own. Once you’re sat down and have decided which of the
menu choices to go for you simply watch the food go past and pick up
anything that you like the look of. In comparison to Yo Sushi there is
not the same range of dishes, and Gili Gulu is lacking some of the more
time-intensive sushi rolls, but at a much reduced price I certainly
aren’t complaining. There’s a choice of rice and raw fish sushi rolls,
with a light spread of wasabe, as well as a few cooked dishes for the
less brave. On the conveyor belt you’ll also find dumplings, plates of
noodles, peas in their pods, and a few ‘tourist pleaser’ dishes such as
chicken on skewers, spring rolls and prawn crackers.

The main failing of Gili Gulu, though, is that at busier times they
often don’t have enough staff. Although the food from the conveyor belt
is self service the idea of free tea refills loses its appeal if you
can’t attract the attention of someone with a tea pot. Not being a sushi
expert I can’t comment on the authenticity of the food but having
sampled the Yo Shushi experience (and it is still more of an experience)
I can’t say I have noticed any shortcomings.

Gili Gulu is a great place for a quick, informal meal before an evening
out or at the end of an afternoon in town. It’s certainly not an
upmarket restaurant but if you’re in the area and fancy some fast but
stylish food then I think it’s hard to beat.

Address: Gili Gulu, 50-52 Monmouth Street, London, WC2

Nearest tube: Leicester Square or Covent Garden.

Review of Gili Gulu on London eats

PHP 5 class constants and subclasses

Another one of those ‘I wish PHP 5 did this…’ moments has occurred to
me with class constants. The addition of constants is good but the
problem is when it comes to subclassing. The code on the PHP 5 site:

class Foo {
   const constant = "constant";
}

echo "Foo::constant = " . Foo::constant . "\n";

is fine. Of course, what you really want to have is a method to give
you the constant in case you want to change the workings later:

class Foo {
   const constant = "constant";

   public function getConstant(){
      return self::constant;
   }
}
$foo = new Foo();
echo "$foo->constant = " . $foo::getConstant() . "\n";

and this works too. The problem is that if you decide a subclass
needs a different constant value, so we add

class Bar extends Foo {
   const constant = "bar constant";
}

If we then call

$bar = new Bar();
$bar->getConstant();

then the value returned is “get_constant” i.e. the value of the
constant in the parent class. This is because Bar has no handler for
getConstant() so it uses its parent. That’s fine, but now we’re in the
parent context then Foo::constant is returned through the reference to
self::. The way to get round this (that I have found) is to put a copy
of the getConstant() method in each of the subclasses. This kind of
defeats the purpose of inheritance in this case.

class Bar extends Foo {
   const constant = "bar constant";

   public function getConstant(){
      return self::constant;
   }
}

Now we call

$bar = new Bar();
$bar->getConstant();

and the correct value is returned. Of course, the other way round is
not to use constants at all but to put the value in a private variable,
but then what use are constants?

MySQL Truncate slow performance problems

I was having problems with a MySQL TRUNCATE taking a long time on a very
large table (with foreign keys). I had thought that TRUNCATE ran more
quickly than DELETE but, according to the MySQL manual, that isn’t the
case with InnoDB tables. In this case there is no difference between
TRUNCATE and DELETE and it’s recommended to drop the table and then
re-create it. That sounds like a very high risk operation to me, but
given that the TRUNCATE statement is taking an hour and a half to run
then it looks like I’ve got some code to write.

TRUNCATE in the MySQL documentation

Restaurant review: Vic Naylor’s, Clerkenwell, London

Vic Naylor’s in Clerkenwell is one of a collection of reasonably
up-market restaurants along St John Street. On entering the
bar/restaurant one is struck by the effort that has been put into the
interior, yet somehow it appears very natural. It’s also slightly more
distinctive than the formulaic modern interiors that seem to be the
default nowadays.

We arrived half hour early and sat for a drink at the bar. There’s a
relaxed feel to the place and a slow trickle of people coming in through
the door filling up the restaurant. Once our drinks were finished it
took nothing more than hopping off our stools and a glance at the
waitress for her to show us straight to our table. This was certainly
impressive attention to customers, especially given that there only
seemed to be two waiting staff for the 30 or 40 seats in the restaurant.

Service throughout was also excellent. We weren’t hurried at any point,
if we asked for a couple of minutes to decide on something then we would
be left alone and, importantly, someone would come back to see to us a
few minutes later. There were no large gaps between courses (unless we
felt we needed the rest) and despite time being shared between the two
staff they obviously kept in touch with who had ordered what dish so
there was no need to ask as they brought food to the table.

The food was very good too. Throughout the three courses on the menu
there is a variation from the more conservative to more adventurous
modern european food, including (on the night we visited) steak, duck
breast and risotto, and prawn and crab ravioli. Each course was well
presented and richly flavoured yet somehow had a lightness of touch as
the different tastes balanced each other.

The only possible downside was I felt that the winelist was slightly on
the expensive side (ranging from around £12 upwards), although that
seems to be par for the course nowadays. The wine itself (a Rioja
Blanco) was good, however, so there has obviously been some thought put
into the selection. Price-wise we came in at just over £40 a head,
including a decent tip, which for three courses, wine, water and coffee
seemed fair. In fact, given the prices of some London restaurants it’s
very good value for money.

Clerkenwell has certainly changed over the past few years. When I first
worked there back in 1998 you certainly wouldn’t think of going there on
a Saturday evening on the off-chance of a good restaurant being open.
Clerkenwell has been transformed into a trendy night-spot (albeit a
slightly exclusive one) and, along with most of the other good
restaurants in the area, booking is advised for Vic Naylor’s.

Address: 38-40 St John Street, London, EC1M 4AY

Nearest tube: Farringdon

Other reviews:

Vic Naylor’s on London Eating

Vic Naylor’s on London Eats

Fantasy football league team

We’re at second in an office Telegraph’s fantasy football league at the
moment, which is pretty good, but one of the directors is in the lead
and apparently has won for the past two years. Our fantasy football team
consists of:

  • Goalkeeper:
    • J Lehmann (Arsenal)
  • Defenders:

    • W Bridge (Chelsea)
    • A Cole (Arsenal)
    • J Carragher (Liverpool)
    • F Queudrue (Middlesborough)
  • Midfield:

    • F Lampard (Chelsea)
    • F Ljungberg (Arsenal)
    • C Ronaldo (Manchester United)
  • Strikers:

    • N Anelka (Manchester City)
    • J Beattie (Southampton)
    • JF Hasselbaink (Middlesborough)

Now the team is doing fairly well, and our defence and midfield are
doing very well, but we’re still having trouble with consistent
goalscorers. Beattie started the season well but Southampton don’t seem
to be on the move (expect downwards) so I’m in two minds about him. Both
Anelka and Hasselbaink have had spurts of scoring well, but we’re still
not getting the striking points we need. Suggestions welcome…

Telegraph Fantasy Football

Review of Yamaha Fazer FZ6 600

Having not ridden a motorbike for some 7 years I sat astride the Yamaha
Fazer 600 with some trepidation. I had chosen the bike as it looked like
it would have a reasonable amount of power to ‘break me in’ to the world
of bigger bikes, but with a less racey style than the CBR’s or an R6.
After the owner of the hire shop telling me that ‘this is a fast bike’ I
was already worried.

In terms of speed, I certainly wasn’t disappointed. I took it fairly
easy on the throttle to start with but after half hour or so of riding I
felt like the full range of power was easily controllable.

Out on the open road the Fazer does have pretty good acceleration. I
know it’s not up there with a CBR 600 or an R6 but compared to most
other traffic on the road it’s more than lively enough. The power band
is not as even as I would have liked, though, with a big kick at higher
revs. Perhaps if I gave it another go it wouldn’t seem so bad but it was
something I had to watch out for for a while.

The riding position is reasonably upright. Although this makes it fairly
comfortable as a sitting position, the faring is quite minimal and the
amount of wind certainly gives you some neck strain after an hour or
two. I’d say the almost complete lack of protection the faring gives is
the lowest point of this bike overall.

The pillion passenger was pretty comfortable, though: enough so to fall
asleep going along the motorway! The other great thing about this bike
is it’s fuel range: I got more than 175 miles out with two people and
luggage, and the fuel tank tells you exactly where you are. This is
certainly worth considering with a bike: a CBR 600 needs a fill up about
every 120 miles in the same conditions.

All in all, it was a pretty good bike but perhaps a bit too much between
stools for me. The riding position wasn’t racey and neither is the
throttle, but the narrower power bands and lack of faring also don’t
push it anywhere near the touring bracket. Where this bike would be
ideal is as a commuter, with it’s good tank range, comfortable riding
position and some good bursts of power for overtaking.

I did like the Fazer, and I think one day I’ll have another go (the R6
just looks a bit too raw) but I think the real crux is that it’s a class
of bike that isn’t quite for me.

The Fazer FZ6 on the Yamaha site

Restaurant and pub review: The Duke of York, Clerkenwell

“The Duke” in Clerkenwell is hidden away down a back street (Roger
Street) just off Theobalds Road and Grays Inn Road, London. Although the
pub comes across as a ‘local boozer’, it’s clear from the thought that’s
been put into the decor that it’s not intended for a purely local
audience. The beer selection is pretty good (a nice selection of ales)
and the wine-list is not too expensive, if still a tad on the pricey
side.

The pub itself is split into two parts: the bar, and the restaurant.
Both are nicely fitted out in a kind of modern-rustic way and it manages
to pull this all off without appearing to try too hard.

Our experience went downhill as soon as we asked if we could sit in the
(almost completely empty) restaurant half: “As long as you’re going to
eat.”. We said we were waiting for friends, and they agreed to give us a
whole 40 minutes before we had to order. 10 minutes later, we were asked
again if we were ready to order and had to remind them that we were
waiting for more people. The friends arrived, but then we had to run the
gauntlet of trying to attract a waiters attention; it was puzzling that
after so much harrassment earlier we were actually finding it difficult.

The food was very good when it arrived, although hardly service with a
smile. Again, attracting someone’s attention for another bottle of wine
was an ordeal. The meal/drinks came to around £25 a head in the end,
which I don’t see is a sum to be sniffed at by a pub/restaurant on a
Thursday evening, and although The Duke of York has a very good quality food and a well thought out eating area, the feeling that we were imposing on the staff left us
with no desire to return again.

The Duke’s website: http://www.dukepub.co.uk
Another review: The Duke review on London Eating

Honda VFR 800 review

I’ve ridden a few bikes over the past couple of years including a Yamaha Fazer 600, a Honda CB 500 and a CBR 600 (a few times now). I was really looking forward to trying out the VFR as I thought it would offer a similar ride to the CBR 600 but with some extra power for a pillion passenger.

First impression was odd: the engine chugs away like a car that’s on the verge of misfiring, but open up the throttle from virtually any revs and away it goes! The linked brakes took no time at all to get used to: pull the brake, the bike stops. Easy.

On the open road the VFR is a very comfortable and very easy ride. The problem for me was it was a bit too easy to ride. The power band is fairly constant, although when all cylinders kick in above 7000rpm there is noticably more pull, but somehow, for me, that takes the fun out of riding a bike. I prefer the ride of the CBR 600 that will pull reasonably well at low-ish revs but really lets rip further up the band.

My other gripe with the VFR is the size and weight. It handles well once you’re up to speed but I never felt as confident in the front tyre as I have on other bikes. I think this may have something to do with the slightly more upright riding position and being seated slightly further back than on a CBR. Or it could just be me. I do know that I managed to lock up a front wheel when a pheasant ran out into the middle of the road and that’s never happened to me on any other bike.

In terms of the ‘technical’ aspects: torque was very impressive, speed was okay, but again I wasn’t impressed by the mileage. My main (possibly only) problem with the CBR 600 is the size of the tank, but I could only get a few more miles out of the VFR and it didn’t seem to provide any extra speed considering the petrol consumption.

From a pillion’s point of view (I was told) it doesn’t feel as solid. Again, I think that may be a combination of height and riding position, but pulling down a straight left my passenger feeling like they were going to tip off the back of the bike. This has never happened on a CBR 600 before and I’ve certainly never held back. Perhaps speed is all about perception and I’ve never managed to pull away as quickly on a CBR with a passenger before; the VFR certainly didn’t seem to notice the extra weight in the same way.

All in all: Honda’s VFR 800 is a good bike, but I don’t think it’s for me. I think my main problems are the size of the bike and slightly more upright riding position. Although the through-the-range power was good, I prefer something that you have to push a bit harder but rewards the effort with responsiveness and a nimble road poise.

Implementing Iterator in PHP 5

There’s a good article on sharepoint about implementing Iterator in PHP
5: http://www.sitepoint.com/article/php5-standard-library/1. I’ve used
it a couple of times now and it’s a great way to treat objects as
collections.

More info about the Standard PHP Library can be found here:
Standard PHP Library with a link to download a windows chm help file for reference.