Not just a website… a government maintained website! http://www.toiletmap.gov.au/
I encountered this while browsing for camping sites around South-East Queensland.
It’s done by a project as part of the “National Continence Management Strategy” of the Australian federal govt department of Health and Aging. There ya go… ;-)

I always tell people that Australian govt is quite good with providing online information, forms, and online services… but this probably takes the cake so far. Mind you, it’s an excellent and useful service.
In places like the Netherlands and Germany, you often have to pay for use of these public facilities.

(note for American readers: a toilet is a toilet. Everyone uses them [one hopes]. It is not an offensive word that requires euphemising (yes I made up that word). It’s not a bathroom, which as the name suggests has something to do with bathing (washing), and often does not facilitate a toilet at all. Thank you.)

Is there a difference? Yes there is, and it’s very significant both in functionality as well as in speed.

COUNT(*) counts rows. If the underlying table is MyISAM, and there’s no WHERE clause, then this is extremely fast as MyISAM maintains a row count of the entire table. Otherwise, the server just needs to count the number of rows in the result set. Which is different from….

COUNT(col) which actually counts all not-NULL values of col in the result set. So here, the server needs to iterate through all the rows, tallying for which rows col has a not NULL value. Of course, if the col is NOT NULL the server may be able to optimise this, but I’m not sure – after all it’s a result set not a base table.

Anyway, there ya go. I spot this with customers, and where possible changing to COUNT(*) can often result in a serious speed improvement. Nice little trick.

What follows is just a suggestion. You may have a perfectly sensible different way of accomplishing the same thing, and that’s fine. Actually, feel free to post your own methods in a comment. Anyway, here we go…

/*appname:module:class:method:symbolicname*/
SELECT tn.olname, AGGREGATE(ot.col) AS aliascolname, ...
   FROM tblname tn
   JOIN othertable ot ON (ot.id=ft.id)
   LEFT JOIN footable ft ON (ft.id=tn.id)
  WHERE tn.somecol = 123
    AND (tn.whatever > NOW() OR ...)
  GROUP BY tn.colname
  HAVING ...
  ORDER BY ...
  LIMIT ...

Putting the comment into the query right at the start allows you to easily find it in SHOW PROCESSLIST or a log file (such as slow query log) and spot where it comes from, even if the end of the query somehow gets truncated. It’s a fabulous tool for keeping track of queries and keeping apps maintainable. If you are a DBA, you will love instilling this behaviour with the developers….

Use explicit join syntax rather than comma; no different for the server, but it’s more readable. I put the join condition (ON or USING syntax) next to the join it belongs to, so it doesn’t get “lost” in the WHERE clause of a big query. You could say INNER JOIN rather than just JOIN, but that’s a detail.
If you actually *need* a cartesian product (no join condition), say CROSS JOIN to make it clear that you didn’t just forget the ON clause…. so people won’t wonder about it later.

Be explicit about which table a column belongs to, with big queries it just becomes difficult to read otherwise. By using shorthand aliases for table names, this is not a big deal. And it’s all the same for the server, this is just for you and others’ benefit.

Use caps for keywords, and use lowercase, underscore_lower_case, or CamelCaps for table/colnames.
Split the query into multiple lines as seems appropriate for readability. You can even insert extra comments with /* … */ at any point.

In expressions, be liberal with use of (). Implicit evaluation order is cool but explicit is more readable. It allows your brain to understand what’s going on without having to make the “what happens first” assessment.

These few things will sooo help you, your colleagues, anyone else ever dealing with your queries or installation later, and if you ask a question somewhere it’s much easier for someone unfamiliar with your app to actually read your magic and help you.
Enjoy!

(just in case – there may well be syntax or other errors in the sample query above; feel free to tell me and I’ll fix it up, just to make sure noone is distracted by such probs – thanks)

In response to popular demand (invited visitors noticing and commenting on the sign next to my doorbell), I’ve put up the text of my Hawkers Notice on my wiki. It’s aptly filed under the Spam category.

That I am, today (in Australia, that is – tomorrow for you Americans).
And since people always ask kids, the question I tend to ask on someone’s birthday is this:

“What do you want to be, when you grow up?”

Go ahead, think about it and put your answer in a comment!