MySQL Bugs: #47786: Add note and/or reference to http://dev.mysql.com/doc/refman/5.1/en/blob.html

Bug #47786	Add note and/or reference to http://dev.mysql.com/doc/refman/5.1/en/blob.html
Submitted:	2 Oct 2009 10:17	Modified:	6 Oct 2009 16:22
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.0+	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Usage of BLOB/TEXT columns may cause performance issues in the database when joins including these columns are made to other tables. This is because the in memory tables can not be used and the tables are written to disk, see:

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

However when looking at: http://dev.mysql.com/doc/refman/5.1/en/blob.html there is no reference made to possible consequences of using a blob type.

How to repeat:
Read the manual.

Suggested fix:
Add reference to http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html and/or a note like those added by Simon Mudd as a comment recently:

"If you include BLOB or TEXT columns in a join with other tables the result of the join will go to disk and NOT be done in a memory table. This can have an important performance penalty and so coders should be aware of this, and unless these columns are really needed avoiding including them explicitly or using SELECT *."

Valeriy, before I make this change, do you have anything to say about the additional user comment posted by Kristian Köhntopp in response to Simon's comment?

I'd say that his comment just explain in details when temporary table can be created on disk. Great comment to have, but still we should add a reference and small note about possible perfromance impact.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Instances of BLOB or TEXT columns in the result of a query that is
processed using a temporary table causes the server to use a table 
on disk rather than in memory because the MEMORY storage engine
does not support those data types (see
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html). Use
of disk incurs a performance penalty, so include BLOB or TEXT columns
in the query result only if they are really needed. For example,
avoid using SELECT *, which selects all columns.