SELECT ... FROM
tblProduct as prd FORCE INDEX(vProductName,iCategoryId,eProductType,vProductImage,fPrice,eProductStatus,dProductDateAdded)
LEFT OUTER JOIN (SELECT bidjoin.* FROM tblProduct as prdjoin, tblBid as bidjoin WHERE bidjoin.iProductId = prdjoin.iProductId) bid ON prd.iProductId = bid.iProductId
, tblCategory as cat FORCE INDEX(vCategoryName,eCategoryStatus,dCategoryDateAdded) 
, tblAuctionProduct as aucprd FORCE INDEX(iRequiredBids,iProductId,eAuctionType,iFreeBidLevel,fAdminFee,dDateClosed)
, tblPurchaseProduct as purprd FORCE INDEX(dProductCloseDate,iProductId,iTotalInventory,dLastUpdated)

I know, unreadable. But the main issue today is the FORCE INDEX. This is just one query (real, with permission - I just adjusted some identifiers) from an app that is litterally full of queries using FORCE INDEX. So what does FORCE INDEX do? It forces the MySQL optimiser to use the specified index (or choose from one of the specified, if multiple), even if it reckons it's not the best choice. Likewise there's an IGNORE INDEX modifier that denies the optimiser the choice for using a specified index in a particular query.

I generally hold that these modifiers should only be used for testing things and tracking down optimiser issues (not that common these days, but 3.23 had plenty that 4.0 fixed). This because when you hardcode them into an app, perhaps fixing a real problem you see *now*, your data will still change over time and so the optimiser needs the freedom to change its choices too. It might be ok for a week, a month, or even a year, but at some point it's going to cost.

The above query takes about 5 times as long compared to the same query without the FORCE modifiers. Considering the app is chockers with it, I'm guessing that it might have been coded by someone who didn't know that MySQL actually has an optimiser at all. Who can say... anyway it serves here as an extreme example.

The take-away message is: use these modifiers with care, and be very hesitant ever adding it to production code. There's generally always a better way to resolve whatever problem you might be observing.

Compared to SQL*Plus (Oracle's cmdline client) which can as I understand produce complete paginated reports, the 'mysql' tool has fairly simple output formatting. I'm not generally fussed about that, as there are perfectly good other tools do make snazzy reports with. Some very cross platform (like web based), output PDF, not a problem. You can even write your own quickly these days.

But the cmdline tool has its use. I always teach my students the basics of it, and insist that some exercises are done using this tool. Familiarity (which involves some practice) helps there. Why? If all else fails, you generally do have mysql available on a machine. It's not hindered by remote ssh logins, or anything else. And, of course, for quickly checking something. And that's where the proficiency helps again. So, normally, you get this type of ASCII table:

mysql> select "Hello, world!" AS foo;
 --------------- 
| foo           |
 --------------- 
| Hello, world! | 
 --------------- 

which is nice. But for some commands, this type of formatting would make the output unreadable. Key examples are SHOW CREATE TABLE, SHOW CREATE TRIGGER, and SHOW WARNINGS (particularly after EXPLAIN). Now check this out:

mysql> select "Hello, world!" AS foo \G
*************************** 1. row ***************************
foo: Hello, world!

If, instead of a semicolon you specify \G, the output is formatted vertically. A separator line above each row, and then each column prefixed by its name/alias followed by the value of that column within the row. It's much more readable for cases where you get a one-row result set with only a few columns, at least one of which might be longer than the width of your console.

The above also teaches us another lesson: the semicolon is not actually part of the SQL command. It's merely a delimiter that tells the client code to send the query to the server. If you're in a programming or scripting language using the MySQL API, you wouldn't include it in the query string.

A related trick is to use the "pager" command. If you specify pager less on the mysql command prompt, the output will thereafter be run through less. So then you can scroll through a larger result set, search it, etc. (see man less).

Final one for this post, there's "tee". This command followed by a filename (or program/pipe!) lets you log everything you do, queries (what you type) as well as output. Very handy, particularly during training and testing, or when you need to make a bugreport or support request.

This fellow is pretty amazing: http://beta.ivancover.com/wiki/index.php/Eee_PC_Internal_Upgrades. To the already tiny Eee PC, he added (internally!):

1. USB hub
   
2. GPS with antenna
   
3. Bluetooth
   
4. Card reader w/ additioal SSD
   
5. Power switch (10 dip) for switching all extra foo on/off
   
6. Wifi upgrade 802.11n
   
7. FM transmitter
   
8. Modem (admittedly there's design space for that)
   
9. Touch screen
   
10. Temperature sensor
    
11. Heatsink
    

That's pretty cool...

Since a few months, sellers can no longer provide neutral or negative feedback to buyers. Not sure if I completely agree with this as I reckon buyers can be quite dodgy and how else to capture that. But the objective was to weed out retaliatory feedback. I.e. buyer has a bad experience, provides neutral or negative feedback, and in return for his honesty gets back similar feedback. So that's no longer possible. I suppose that's good.

I actually had such an experience at the beginning of the year, before the new policy. That one (in this case neutral) retaliatory feedback seriously impacts my rating to just over 90%. Not much of an issue as a buyer, but if I want to sell something, potential buyers might regard me as dodgy just on the basis of that number. Of course they could delve into the exact feedback comment and figure out what's been going on to some degree, but who bothers with such detail while browsing eBay for goodies?

The formula for the rating is positive / (positive+neutral+negative), and all that limited to the last 12 months. Let's take my simple case where I get one neutral. For 11 transactions over the last year, you get 10/(10+1) = 90%. Ok so I don't buy stuff on eBay all the time. Most people don't. But let's double the number of transactions and see how the formula works out: 20/(20+1) = 95%. Still not good for seller karma. Anyway, you get the picture; this kind of thing is damaging, and you'd really have to go on a buying spree to make up for it. With different buyers or on different weeks, otherwise it's counted as a duplicate and disregarded. And heaven beware if any of the many different sellers also decides to give you a neutral rating!

Ok ok, so the new eBay policy prevents this exact scenario from occurring again. But consider the mechanism if you're a seller. Buyers *will* provide you with feedback, and using the same formula a single malicious buyer can destroy your reputation. You suddenly have to sell a lot more to make up for it, except you may not be able to, because of your rating! In a nutshell, unless you're a powerseller moving dozens of items a month, you're pretty much an open target. If you, like I, sell the odd item once in a while, you don't have a chance.

But hang on, isn't there a policy that allows you to get feedback removed? Yes, but only in very specific cases. For this, the comment would have to be defamatory, referencing an unrelated item/transaction, a policy file number, a URL or other contact details, stuff like that. Also, a clearly negative comment on a positive rating qualifies. But not the other way round.
So, the loophole is a gaping one, and that is: you just scribble something nonsensical or at least meaningless; that way it won't qualify for removal. So you can just give a seller a neutral (just as damaging as negative, as far as the formula is concerned!) with a comment of "blah de blah" and that does the trick.

Someone please tell me that I went wrong with my logic - and where!

Horay! Thank goodness, and about time too!

On the latter, I do appreciate that any such switch is a serious change for a development team, I was involved with this while at MySQL so I'm well aware of the issues ;-)
Nevertheless, it was high time as it once again allows the rest of the world (outside of Sun/MySQL) to grab a repo and peruse the history properly. Long time ago (before bitkeeper turned fully evil) this was possible also. See MySQL is using Bazaar and Launchpad for their source code.

We had a distributed revision control talk in Bris a few weeks ago, by Ian Clatworthy of Canonical. We managed to do a video, but it's not yet online. Various attendants including Damian Hickey (of the Freeway ecommerce project) now want to move to either bzr (Bazaar) or hg (Mercurial) as well, which is great (yes git is ok too). Move from what? Subversion. It's a different way of working.

Look, subversion is better than cvs, but that doesn't mean it's brilliant. It seriously hinders and borks development processes, particularly for teams. Changing over is, particularly with bzr, quite easy. bzr can start operating in a similar setup to svn, and then you can shift across to the additional beautiful features it has. Good stuff.

I acquired an Eee PC for my Phoebs. On special somewhere, unfortunately not a pink one ;-)
While I see that lots of people are replacing the Asus/Xandros OS with Ubuntu for Eee or other distros, I've decided to stick with the default for now. It's a very easy desktop to use, with all clutter, confusion and traps disabled but still accessible through other means. Traps you ask? Yea... traps. A three-year-old with a mouse is a dangerous mix ;-)

So, why have a computer for a 3 year old anyway, and why an Eee, and ....?
Phoebe is already quite aware that work generally involves using a computer, so she insists having one on her little play desk: "for work"; that's no reason to give her one though. Eee starts very quickly, being flash based and in its default simple desktop mode. Smurf gets easily bored so any regular machine just doesn't cut it (believe me I've tried).

But kids these days must to become computer literate. Being able to use a mouse and a keyboard is not an optional extra. So, letting them play with it from early on, it's a natural thing rather than something complicated later.

I installed GCompris and put it into the Learn menu, as Phoebe loves to play with the mouse exercises.
She also liked typing, but I haven't yet found a program that allows just simple "keyboard bashing" without icon clutter (more traps). Suggestions welcome - perhaps an add-on for GCompris?

And no, I won't be installing MySQL on this machine!

You still have some time to get your proposal in for OSDC 2008 in Sydney. Yes I lead the conference organisation last year, but there's a whole new team for this one and I can blissfully be an attendee or, if at least one of my proposals gets accepted, a speaker.

Last year was actually also my first time at OSDC, and it's really a great event... by developers, for developers... about creating OSS, or using OSS to create other stuff, and about the processes involved. You learn heaps. Remember, the worst killer for learning something, is to say "I already know that".

There's heaps each of us doesn't know, and OSDC is an ideal place to share your own experience and pick up some more info for your wetware as well as tune it, and of course have heaps of fun. OSDC is also lunches and other munches, the included-for-all conference dinner, and just the random chats in the corridor. Not forgetting the lightning talks at the end of each day, that's like a whole special OSDC culture!

Perception and reality don't always converge with users when it comes to MySQL licensing, even with Sun/MySQL focusing on selling its MySQL Enterprise support subscriptions. Just today I was talking with a client who thought that to get a MySQL Enterprise subscription, it was required to purchase licenses.

Not true. Simple. They hadn't talked with a MySQL sales person, so this wasn't the (usual ;-) warping of reality that I tend to see, but somehow perusing the information on the net they'd come to the above conclusion. Easy to dispell once understood, but much more complicated (if not impossible) to figure out how they came to that conclusion in the first place....

They also didn't realise that the MySQL Enterprise binaries are also GPL. Ohwell.

But really, nobody wants to care about licensing, it's this necessary evil that you have to deal with. Best to keep it simple, or even better, make it a non-issue! MySQL's subscription model was intended to do just that. Didn't quite work out that way - but I do believe the intent was good at least in that respect.

If you're around Brisbane, do join us next Tuesday evening for a combined MySQL/PHP meetup... info at http://mysql.meetup.com/84/

Ian Clatworthy (Canonical) will be presenting on Distributed Version Control, such as is used in Bazaar, Mercurial and Git. You may be using SVN/SVK now, but DVCS is definitely worth a look; it's a different approach that makes you (and your team) more productive, and avoids many common version control problems (think "everybody stop working we're doing a merge", or "eek I had this right half a hour ago").

To give you an idea of how important this stuff is.... a brief snip from a conversation I just had with a developer:

D: I was wondering whether you got to set up that mercurial repository?
A: sorry not yet. but I presume you've created a local one with hg init so that can be pushed at any time when I create it, it won't delay anything.
D: oh didn't know it worked like that
A: it does. your repo is identical to the host's.

In a nutshell, with distributed revision control, when you need a new repository you just create one locally, you don't need to wait for someone to create one on a main server. It can, at any point, be synchronised there.