COUNT() function returns a number of rows returned by a query. In a popular opinion COUNT(*) should not be used on InnoDB tables, but this is only half true.

If a query performs filtering on any column, there is no relevant difference in how COUNT(*) will be executed regardless of the storage engine. In any such case MySQL has to look for matching rows and then count them.

In the following queries COUNT(*) can be used without any negative impact on performance:

SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled = 1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND is_enabled = 1

The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value …

With the 3rd edition of High Performance MySQL finally complete, I’ve begun work on the 4th edition. As you know, technology moves much faster than printing presses, and a book is outdated very quickly, so this is a never-ending project. I’m also outlining the 5th edition in anticipation of starting it immediately afterwards.

I’m looking for your input on what I should cover in the new edition. Should I discuss MySQL’s intra-query parallelization across multiple CPU cores? Should I explore how cloud computing platforms enable higher performance at lower cost than dedicated hardware? Should I explain the bizarre bug in MySQL’s datetime type that causes it to skip the day after March 31st every year? What are your suggestions?

Further Reading:

• …

MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1

However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance  and forced to build complicated queries based on joins rather than on subqueries.

Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.

You’d say it’s not a problem for typical OLTP, web based traffic at all, just don’t use subqueries! That’s true, …

O’Reilly authors get 10 copies of their own books for free, and my copies of the third edition of High Performance MySQL arrived yesterday. Now it’s official! It feels nice to actually hold it in my hand.

A few people have asked me about messages from Amazon saying that their ship date has changed. I don’t know anything about that; maybe Amazon just made a wild guess the first time and now they actually know something more realistic. Or maybe the book is more popular than expected? It’s currently at position #10 in the SQL category on Amazon, which seems pretty good to me. No “DaVinci Code” to be sure, but not bad for a technical book.

Further Reading:

• High …

I’ll be speaking at the Shenandoah Ruby Users Group Monthly Meetup tomorrow in Harrisonburg, Virginia. The topic is “Seven Things To Know About MySQL Performance.” See you there!

Further Reading:

• Speaking at the Shenandoah Ruby Users Group
• Central Virginia MySQL Meetup has reached cruising altitude
• New Ruby Conference: Ruby on Ales 2011
• …

Wow, I completely forgot to advertise this. I’m speaking Saturday (tomorrow) at RubyNation, which is already well underway (I’m missing the first day, though). My topic is “Seven Things To Know About MySQL Performance.”

Further Reading:

• I’m speaking at Surge 2011
• Speaking about Maatkit at CPOSC
• Speaking at Enterprise LAMP Summit 2009
• Speaking at Surge 2010
• …

While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:

ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP

They had substituted * for a .+ in a metasequence. A metasequence is a parenthetical expression that evaluates based on multiple alternative conditions, and the pipe (|) acts as an OR operator. The full code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer Handbook. The student’s change would have worked without an …

I guest-posted on Fusion-io’s blog about the database’s working set size and the interplay with fast Flash storage. It’s written from a MySQL point of view, but it’s applicable to many types of systems.

Further Reading:

• 6 ways to stay sane while working from home
• How to understand SQL joins
• How to understand key length limitations in MySQL
• …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn’t matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you’ve seen it, it’s really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I’d capture it here in case it’s helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here’s the staff table from the database (today’s imaginary data isn’t particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | …