MySQLing, Wordpressing and Internetting

This morning I went on the Internet quite a bit, mainly reading The Luminous Landscape recent articles.

I also did some more work on my short URL creating MySQL function, writing a few different versions and benchmarking them against each other.

In the afternoon I did some more work on my photo website Wordpress blog, trying to add the facility for it to use the short URL service.

In the evening I carried on working on my photo website Wordpress blog, trying to add the facility for it to use the short URL service. I modified the short URL service so it could accept an array of long URLs to convert. This means that when you initialise the short URL service in Wordpress, it can send off an array of all your existing permalinks and get back the short URLs for them.

If the short URL service didn't accept arrays, then Wordpress would have to send off a request for each permalink to the short URL service, which could be quite time consuming and bandwidth wasting.

However, when I had got my service to accept arrays, I found it wasn't working - only the first URL in the array would be converted to a short URL, all the other URLs would get an empty string for their short URL. After doing some debugging, I found the problem was that I was using a variable in my MySQL function, and so this variable would still exist and be shared by subsequent runs of the function on the same database connection.

Strangely, I didn't get this problem when BENCHMARKing the function earlier, it only happened when running the function multiple times in a row normally.

So I wrote yet another version of the function that didn't use an @variable, but rather a variable limited to the function scope (using DECLARE). After writing that, I BENCHMARKed it, and it was very slooow compared to the function I'd been using before.

After doing that I updated my thread on the Sitepoint MySQL forums that I had started earlier today, saying about the problem, and asking if my new function could improved due to being so much slower than the old function.

But then I realised that when you run a function multiple times in a row, MySQL probably wouldn't be running the functions concurrently, and so the variable being global or local in scope wasn't important, so long as it was reset to NULL at the start of the function. So I modified my previous function to set the variable to NULL at the start, and BENCHMARKed it.

Weirdly the function was now actually slower than the new function that uses a local variable I'd written. I guess the reason must be because I benchmarked the old function earlier, and was benchmarking the modified old function and the new function now, so my computer must be a lot slower now than it was earlier.

I'll probably benchmark all 3 against each other tomorrow to make sure. I also found something else (not) interesting. In my new function I had to use an exception handler, the exception handler selected a value from the database and then returned, so whether the exception handler continued or exited didn't make any practical difference. I benchmarked the same function with CONTINUE and EXIT handlers, and found that CONTINUE was actually faster.

The weather today was rainy overnight, nice and sunny in the morning, but then clouded over in the afternoon and was rainy in the evening.

Food
Breakfast: Orange marmalade toast sandwich; cup o' tea.
Lunch: Roast beef with mustard and crunchy salad sandwich; a few Taco (or sumat) flavour Japanese Doritos; Banana; Slice of Tesco all butter Madeira cake; Rocky; cup o' tea.
Dinner: Slice of quiche; vegetable flavour rice; Mexican flavour rice. Pudding was 2x home-made milkybar button muffins. Coffee; 2x pieces of Sainsbury's Caramel Chocolate.
Supper: Dark chocolate digestive; hob-nob; cup o' tea.