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:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.7 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[7 Dec 2004 13:42] Daniel Schneller
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.
[7 Dec 2004 14:08] Heikki Tuuri

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.


[14 Feb 2006 14:18] Valeriy Kravchuk
Thank you for a reasonable feature request.