MySQL Bugs: #7074: InnoDB slow on select with Blobs that are not in where clause

Bug #7074	InnoDB slow on select with Blobs that are not in where clause
Submitted:	7 Dec 2004 13:42	Modified:	14 Feb 2006 14:18
Reporter:	Daniel Schneller	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	4.1.7	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Select on a table that has a BLOB column is very slow if the BLOB column is included in the selected fields, even if it is not part of the WHERE clause.

How to repeat:
Table structure:
intA, intB, intC, blob

select intA, intB, intC, blob from tblname where intA=7; (takes 11seconds)

select intA, intB, intC from tblname where intA=7;  (takes 0,2seconds)

Both return an empty set.

How can this be? It seems as if all columns are first read an the where clause applied only after that.
The table has no index on intA, but as the query returns no rows this should not make any difference, should it?

Suggested fix:
First check the condition on intA without reading all row data. Then read the remaining columns for the matching rows.
This might only be sensible for columns with blobs to save disk seeks.

Hi!

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

I am changing this to a feature request.

Regards,

Heikki

Thank you for a reasonable feature request.