A while back I was doing some performance tuning on MySQL 5 for a customer. A SELECT was counting the number of rows in the table. I always use COUNT(*) for that but I know a lot of people, including the customer, use COUNT(pk_col). The query was taking a long time (a few minutes). I analyzed that the problem came from the usage of COUNT(pk_col) instead of COUNT(*). With COUNT(*) it was instantaneous.

I didn’t know that there was a difference between the two. There is no difference in their semantics, therefore it didn’t occur to me that there might be a difference in the way they were executed.

Just to recap the SQL syntax:

• COUNT(DISTINCT col) counts the number of distinct values that are in “col”
• COUNT(col) counts the number of rows where col is not null.
• COUNT(*) counts the number of rows in a table

It follows that for columns which don’t have any “null” values in them, COUNT(col) must be the same as COUNT(*). For any column marked “not null”, such as a primary key, this is always the case. And thus it follows that COUNT(pk_col) must always deliver an identical result to COUNT(*).

However, the database in question was executing a COUNT(pk_col) query and a COUNT(*) query differently.

• For the COUNT(*) queries it was simply counting the number of rows in the table (taking constant time and not requiring reading the rows from disk)
• For the COUNT(pk_col) it was going through all the rows, presumably checking if the pk_col was in fact null, and counting the number of rows where it wasn’t, thus requiring a lot of disk access.

Alas I didn’t write down the output from the EXPLAIN statement and I’ve just tried it on some other databases (using MySQL 4.1 and 5.0) and was unable to reproduce this behaviour. So this is a bit of a useless blog entry! Nevertheless – you must believe me – it did happen!

So the conclusion is, one should always use COUNT(*) and never COUNT(pk_col).

I know the rules for Oracle row locking well. A row can be locked for write if one updates it, or if one “select for update”s it.

1. create table a (x number); (and equivalent in MySQL for InnoDB)
2. Session A: insert into a values (9);
3. Session A: commit;
4. Session A: start transaction (in mysql)
5. Session A: select * from a where x=9 for update;
6. Session B: start transaction (in mysql)
7. Session B: select * from a where x=9 for update;
8. Session B hangs, waiting for row-level lock to be release from the row by Session A
9. Session A: update a set x=4;
10. Session A: commit;
11. At this point, Session B returns no rows. Lock has been released, and row no longer confirms to where, so is not returned.
12. Session A: update a set x=5;

This is where the difference occurs:

• Oracle returns the row to session A. The command “for update” in session B did not return any rows, and thus no rows were locked, and thus session A has nothing to wait on.
• MySQL (version 4.1.18) blocks session A waiting on the transaction in session B to be ended. That means A requires a lock owned by B. But what is this lock? Is it a row-level lock on the row which was not selected? Some other type of lock?

The reason I tested this was it just occurred to me that in a “select for update” the where clause is applied twice:

1. Firstly to determine which rows to return. A lock is requested for those rows, which can involve waiting until the lock is released if it is owned by some other transaction.
2. After the lock has been acquired, the rows is checked again to see if it still confirms to the where clause. If not, it is not returned (although the session blocked waiting for it)

I suspect that both databases simply do step 1 involving waiting and acquiring the lock on the rows which initially conform to the where clause. And both apply the where in step 2, as in both cases no rows were actually returned dispite the wait. But Oracle, upon noticing the row will not be returned, actually releases the lock as the command implies only rows returned will be locked. But MySQL just leaves the lock form step 1 hanging around, which is wrong.

From our Oracle test instance:

1360965 rows created.
Elapsed: 00:01:17.90

That's 1¼ minutes to insert (and index) over 1¼ million rows.

And this is a very old test instance. I think the hardware was last updated 2-3 years ago.

That's pretty quick!

I’ve often had discussions with people about whether the “enum” type in MySQL is a good thing or not. Basically there are two ways to use your database:

1. As an unstructured bunch of “stuff” to store whatever the software needs to persist. Such databases use lots of “blob” data with serialized objects (it’s easy to program), tables with multiple functions (“object” table with “type” column), few constraints, and so on.
2. As a representation of the data the program is trying to model. Such databases have meaningful column names, two different types of things are two different tables. Adding constraints is easy.

There are good arguments for both. As one is programming in a programming language, whatever is most convenient for that programming language (e.g. serialize a bunch of objects to a blob) means less effort, less code: which means less cost and less bugs. Those are all good things.

But for some reason I’ve always been the 2nd type. I like to look at a database and understand what data is being modeled. It creates a certain self-documentation which can often be lacking from complicated software. Constraints can be added which acts like functional assertions (functional in the sense that they involve no state: you say that this value must be between 1 and 10 and then it is that way. You don’t have to program any “path” or state to check that).

That an item can be in exactly one a distinct set of states is a fact of life in all types of domain modeling:

• What state is this invoice in? Is it “paid”, “open”, “paid+processed”?
• Is this item deleted or not? (Or marked temporarily suspended, pending administrator checking the content)
• Is this photo public, or does it belong to a user?

To explicitly work with enumerations is to acknowledge their existence within the domain, and to create a more 1:1 mapping between the domain and its implementation. That’s what all software should be about, modeling. Ideally a program is nothing more than a model and a few rules for how that model transitions between one consistent state and another.

If you program databases like #2 above, like I do, then enumerations really do make the data model richer.

• The database maps to the domain easier (thereby documenting the domain, in case this is not done elsewhere)
• The database maps to a domain-consistent implementation in the programming language easier
• The database implicitly then has a constraint, as you can’t set the column to be some value which makes no sense for the domain (and therefore the program)

So enums in DBs are a good thing, and should be used wherever possible. Oracle should implement them just like MySQL does. And enums should be used in programming languages too. Why enums appeared only since Java 1.5 I don’t know. And the fact that php, perl etc do not support them, is yet another reason not to use those programming languages to try and attempt to build any software system whose function it is to model a domain.

It can be said that then it makes it “more difficult” to add a new state, as now you have to “change the database as well”. But if the model changes, and the database reflects the model, then that’s a good thing, not a bad thing. And it isn’t even much effort: If you say that invoices can be in a new state, then there’ll be a lot of programming work to support that change (UI, billing logic, robots, test scripts). The “alter table” statement is no work at all proportionally, and you’ll also be thankful for every extra constraint the database or programming language can offer you (which such a change feels very scary in a Perl program, but not so much in a Java program).

(Enums are supported in PostgreSQL since v8.3)

There are times when one need to prevent a certain “critical section” of code from being executed by more than one process on the same object at the same time. For example, if the requirements state that a user cannot have two subscriptions of the same type active at the same time. So to enable a subscription, if one does “a) check if user can have the subscription, b) enable the subscription”, one need to make sure that there aren’t multiple processes doing that simultaneously.

Oracle (and thus MySQL InnoDB and PostgreSQL) support row-level write locks. This means that (in contrast to other databases) if one database connection, in a transaction, writes to a row (but has not commited yet) then other connections can freely read the row: they get the version which was most recently commited, which was the version before this transaction. Only if a second connection tries to write to the same row, will the connection wait for the first connection to perform a commit or rollback.

The “select for update” statement acts like a “write” above. All rows which are returned from the select to a first connection are not being written to by any other connection, and any other connection which later attempts to write (or “select for update”) the rows will wait until the first transaction has commited or rolled back. Locking rows using “select for update” is thus an appropriate mechanism to implement locking.

As “select for update” still can only lock existing rows, one needs to decide what the appropriate row(s) are to lock for a particular operation. Remember that the rows are only locked to other connections using “select for update” or writing to the row: anyone can still read the rows. If one wishes to exclude the possibilty of a user having two subscriptions of the same type simultaneously, one cannot lock the “zero rows” the user must have in the subscriptions table beforehand, as only existing rows can be locked.

It thus makes sense to lock the “user” row representing the user (or some other table containing exactly one row per user).

Therefore, for certain critical operations, such as adding a new subscription line for a user, I shall perform the following operations:

1. Open transaction
2. “Select account_id from account where account_id=?”
3. Does the user already have a subscriptions row of this type currently active?
4. If not, create the new row for the user
5. Commit

Note that with other database engines, simply reading the row within a transaction may be enough for it to acquire an exclusive lock. Or worse, for the entire page (Informix?) or entire table (MySQL MyISAM?) to acquire an exclusive lock.

And even if it’s just the row being locked (best case), other reads (e.g. a user just wants to view some data) will cause the reading process to block, increasing the number of processes waiting from just those needing to wait due to execution of a critical section, to all processes accessing the table.

A friend just asked me:

I have a DB, informix actually but I think its unimportant. A column is a char100. I have a string of text in a row in that col. the string is 4 characters long. When I select the char100 column I get a space padded string of 100 characters with my string at the front. Have you ever seen this?

Yep that's normal. A char(100) column is exactly that: 100 characters, no more, no less. So if you put too few characters in the field, rather than giving an error, the db pads the value with spaces.

The "very advanced" datatype varchar (or varchar2 in oracle) remembers how many characters you put in the field. (I shudder to think what oracle's varchar datatype must have been like. These days it's an alias to varchar2).

There are basically no reasons to use a char. We used e.g. char(2) to store a user's country in uboot ("at", "de" etc) but in fact we even had some problems with that, so we decided to always use varchar thenceforth.

In MySQL, if you define a table where it can pre-compute the byte-width of a row in advance, i.e. it's only composed of chars, ints, etc, no varchars, then if you delete rows and then re-insert rows, you never lose any space. As it can slot new rows exactly into the space taken up by old rows.

But if you use any varchars, then it can't. And in that case, it states there is no advantage to having any chars. So if you have a table with some chars and some varchars, then it converts those chars to varchars.

Incidentally, my book on Oracle (which serves also as an advertisement for Oracle) says that Informix, if you do an insert, then the page where the row gets written gets locked for the duration of the transaction, i.e. page lock not row lock. So if you do another insert from another transaction, even though it's an independent row, it's probably going to be written to the same page, so it must wait on the first transaction. So there's a lot of waiting going on. Not being able to do two inserts on the same table from two different transactions simultaneously would seem to be exeedingly rubbish. So I'm curious if it's really true or just Oracle marketing. Or maybe it was the case in Informix 1.0. But let's not forget that Oracle 1.0 probably didn't have varchar2.

Update: my friend writes: spoke to our informix guy, he says that was true in informix 7.10 from '97.

Ah MySQL (at least MyISAM) so isn't a real database!

Firstly, when doing an insert, I did some arithmetic. The numeric column was of a certain width. If the result of the arithmetic is larger than the maximum allowed value my number was just getting turned into that maximum allowed value, without warning or error. A large number suddenly becoming some other large number may sound good in the philosophy of "errors are bad – we want to minimize errors!" but literally it's never what you want. Oracle gives an error if a number is too big to be stored in a column. Which is what you always want.

Secondly, due to above arithmetic overflow errors, my insert statement was failing (as multiple values that should have been distinct, but beyond the maximum, were then identical, equal to the maximum). I kept on doing it and it kept on failing. Then I looked at the table and each time I'd done such an unsuccessful insert (a single statement to insert maybe 10k rows) some rows (but not all – due to the error) were getting inserted. Having half a statement succeed is never what you want! Oracle sets an invisible checkpoint before each statement and if the statement fails, rolls the database back to that checkpoint. That's always what you want!

This just annoys me so much. The sum of an empty set of integers is zero, not undefined.

However neither Oracle nor MySQL understand this. I can only assume this variation from common sense and mathematics is considered the “best practices” definition of the SQL SUM function.

mysql> desc email_box;
| box_size_bytes | int(10)     |

mysql> select count(*) from email_box;
|        0 |

mysql> select sum(box_size_bytes) from email_box;
| NULL |