Saturday, November 18, 2006

MySQL/InnoDB slowness with Blobs

Reading about Peter Zaitsev's feature idea about Finding columns which a query needs to access - which I would really like to see implemented - reminded me of a bug report I filed in 2004 and which bit me again only a few days ago. You can find it under Bug #7074 in the MySQL bug tracking tool. Although it is filed as a feature request, I think one should be aware of this, as it may cause problems in your applications (it did in ours).

Basically it is about explicitly specifying which columns you need in a result set, instead of just using SELECT *. This is generally a good idea, however if the table contains BLOB columns, it becomes even more important, as it may affect performance heavily in an unexpected manner.

From the bug report:

MySQL first reads all the selected columns, and only after that checks the WHERE.

This may lead to long running queries, even if you do not use the BLOB column in the WHERE clause and even if there is no data to retrieve based on the query conditions.

For more details see the bug report.

No comments: