MySQL Bugs: #840: Slow Queries

Bug #840	Slow Queries
Submitted:	14 Jul 2003 2:17	Modified:	15 Jul 2003 6:53
Reporter:	Stephan Schulmeister	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23.52	OS:	Windows (Win2K Server SP2)
Assigned to:		CPU Architecture:	Any

Description:
Having slow Queries when quering mySQL DB for a longblob Column.
Speed varies as if something is blocking mysql some time.

Changing Ports have no effect.

Does anyone know if there is a Win2K Service, that blocks mysql?

How to repeat:
send a mail to schulmeister@apropros.de

Sorry, but this doesn't qualify as a valid, repeatable bug report.

There could be many different reasons why a query time is varying - server load, locking issues 
with other queries being run on the same table, etc.

I have same problem with Windows 2000 + MySQL 4.0.13. Quering table with LONGBLOB data is VERY slow. Even if I don't include BLOB field in the query.

Here my investigation about:

Test table:
ID INT; S1 VARCHAR(100); S2 VARCHAR(100); S3 VARCHAR(100); B1 BLOB

I created 10 records with empty BLOD fields.

I tried query:
SELECT ID, S1, S2, S3 -> 0.03 sec

I put files about 30Kb in the BLOB fields of every record.
SELECT ID, S1, S2, S3 -> 0.05 sec

I put files about 300Kb in the BLOB fields of every record.
SELECT ID, S1, S2, S3 -> 0.10 sec

I put 17Mb file in the BLOB field for only one record.
SELECT ID, S1, S2, S3 -> about 3 to 6 sec.
Ooopppss! No good... I think this is a bug :-(

I changed all VARCHAR fields to CHAR
SELECT ID, S1, S2, S3 -> &#1086;&#1090; 0.6 sec.
It was better, but still not good. Adding more big files (in different records) resulted to larger time :-(

So, using more simple table can solve problem, but not entirely. Let's try to solve this problem by another way:

I created 3 indexes for every CHAR field.
SELECT ID, S1, S2, S3 -> &#1086;&#1090; 0.6 sec. 
OK. System didn't use it as covering index (i think so).

I created index ID+S1+S2+S3.
SELECT ID, S1, S2, S3 -> &#1086;&#1090; 0.02 sec.
OK. System use it as covering index!

So, you can use covering index for solving the problem, but I think mySQL have to work better without any index (there were only 10 records in my table!)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

MyISAM tables always read the whole row from the disk, even if some fields are not used in the SELECT. InnoDB tables can read only some fields, thus skipping BLOB in your example. That's why MySQL supports different table types - you have the choice :)