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.

MySQL load data infile and foreign key constraints

I’ve been up against a(nother) MySQL 4/PHP 5 ‘not doing all I want it
to’ type problem. This time, it involved trying to do a bulk update on
potentially tens of thousands of lines. Now, I don’t want to do an
INSERT for each line and I’ve already written some classes to handle
outputting data objects into csv files which handles all of the primary
keys. There’s an option with LOAD DATA INFILE to REPLACE values where
primary key conflicts would occur so I thought if I used that with
existing rows then I should be okay.

What I have found, though, is that LOAD DATA INFILE with REPLACE appears
to try to delete the row and then re-insert it. This breaks other
foreign constraints and so the database engine won’t let it. Fair
enough, but it would have been nice if the manual mentioned it.

So: next step is to disable the foreign keys temporarily with SET
FOREIGN_KEY_CHECKS=0. From the command line this works fine, but it
seems that within PHP the foreign key checks are re-enabled
automatically after each command is run, which means the net result of
running SET FOREIGN_KEY_CHECKS=0 is absolutely nothing. So when I try
and to the LOAD DATA INFILE the constraints are still there and it
doesn’t work.

If we trusted MySQL 5 fully yet (and wanted to go through the pain of
installing it) I could probably write a stored procedure, with whatever
headaches that would bring, but as it is I’m stuck with MySQL 4 for the
moment. The solution I’ve come up with is to write the SQL query to a
text file and then use an exec() command from within PHP to execute the
commands in the file. I don’t like it, and it doesn’t feel very
portable, but it seems to work. In fact, it seems so handy that I’ve
created a static method so I can do it with any batch of SQL queries.

The handy class follows (with constants in place of the database
names/connection values/temporary folder):

public static function multipleDbQueries($sql)
{
 $file = fopen(TEMP_CSV_LOCATION .
  "temp_query.sql","a+");
 fwrite($file,$sql);
 fclose($file);
 exec("mysql -u " . DB_USERNAME .
  " --password=='" . DB_PASSWORD .
  "' " . DB_NAME . " < "
  . TEMP_CSV_LOCATION . "temp_query.sql");
}

Motorbike hire in east London

I usually hire motorbikes from a place called Raceways in Surrey Quays, East London
but a) it’s quite expensive,
and b) they’re the wrong side of the river. I’m going to be on a Honda
VFR800 this weekend

I’d definitely be interested in finding something a bit closer, though, but East London doesn’t seem particularly well served by motorbike hire.

Hiring an ASP.Net developer

My company is looking for an ASP.Net developer, so here seems as good a
place as any to put an ad. Job description below:

London based Internet development company seeks permanent ASP.NET
developer with an absolute minimum of 1.5 years production experience.

Desktop application programming and web services experience are a must
as is extensive VB.NET and SQL Server 2000 knowledge. ‘Can-do’ attitude,
a problem ownership mindset, excellent common sense and the ability to
communicate clearly to internal and external clients are also
pre-requisites.

Exposure to SOAP and programming languages such as C#, XSL and PHP would
be a distinct advantage.

This is an excellent opportunity to join an ambitious, young and
profitable business at the beginning of an exciting growth phase.

No agencies.

Email: asp dot net at exponetic.com