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.

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Short questions with somewhat involved answers. In terms of MySQL (and databases in general)…

1. What is a character set?
2. What is a collation?
3. How do they interact?
4. Is this something you even need to worry about?

(more…)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Question: Where can you find a really cool Pop Quiz — like question on the syntax (and pitfalls) of the CASE statement?

(more…)

The new program wants to strengthen the relationship …

One of the major additions to MySQL 5.1 is the the event scheduler. It is an internal scheduler, which does not need any help from the operating system. As such, it works independently in every platform.
One drawback of this feature, though, is that it can't communicate with the operating system. i.e. the event scheduler can't read system files, can't send e-mail messages, store data into log files. It can only work within the database server. This is convenient for security, but it is quite limiting. Time for hacking!

In getting started with MySQL Proxy I showed an example of how to run shell commands from any MySQL client. Unfortunately, this method can't be used with the events, because events can't send queries to the …

Ever thought about dialing someone when you're three-sheets-to-the-wind to let them know how you really feel?  Now instead of calling your ex, your boss or your soon to be ex-boss, all-star MySQL web developer Dups has created the St Patrick's Day Drunk Dial phone line.  Now your innermost drunken thoughts are safe and stored with millions of web listeners around the world.

I tested out the service after a few celebratory MySQL vodka shots and it works as advertised. I just hope I don't win.  Otherwise I'll have to call back and beg to be disqualified.  It's a great service to society, but I can't help but wonder: What about the other 51 weeks of the year?

Check it out.  I'm sure this is powered by open source.  But remember, not every web site is gonna win MySQL …

A

I’ve had a lot of feedback from my resignation at MySQL. A lot positive, a lot of shock, even some avid discussion on why, and trying for me to reconsider my decision. Roland my evil twin (it’s a funny story), really challenging me which I very much appreciated, ensuring I had considered the multiple paths before me. I am a man of my own convictions, so reversing my decision was simply not an option on principle.

So what do I want in an ideal working environment. What was lacking, that I could not see and that I’m seeking. Here are two key points.

• “Continual improvement, 1% a day, review in 3 months.” This includes a commitment from the entire team, and a system to contribute, discuss, plan and see results. Something I’m a most firm believer of and will be ensuring is in place in my next position where I will have the responsibility.
• “Your employees are …

I frequently see a question fly past on why an expression like WHERE col = NULL does not come up with the desired result, even though it superficially looks perfectly sane. To address this, we can recap some high school maths, and at the same time finally find out that some things learnt there are actually very practical and useful later in life... I mean, who would have guessed that learning about sets and Venn diagrams was really giving you the fundamentals of relational databases?

So anyway, the proper form would be WHERE col IS NULL. NULLs can't be compared in the normal way, because NULL is not a value. NULL is the absence of a value. If you show this visually, the domain of valid values for a column lies inside the circle of a Venn diagram, while the NULL is outside. It's not a member of the domain.
There are also other functions …

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example - The error message in case of syntax error gives you information about tokens near by but little details:

PLAIN TEXT SQL:

1. mysql> SELECT * FROM  user oder BY pwd;
2. ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'by pwd' at line 1

It would be much better if MySQL would give error give exact position of error (with complex auto generated queries line number is often not good enough) as well as give some better explanation on what is wrong.

The new parser for MySQL was spoke since 5.0 times but it never took off and I'm not seeing it on public road map either.

Though this is just tip of the iceberg of not so helpful or misleading error messages. …