Hi, > Well, two possible explanations... BLOBS are either stored in a > separate area thus forcing MySQL to perform an extra seek to retrieve > the full row or the table scan takes a lot more time to execute because > of the BLOB data you have to retrieve...
I'm not often retrieving the BLOB value in my queries, so wouldn't expect to get the second seek performance it, and hence would expect good peformance. > Reading 100000 rows of 1K is > way faster than retrieving 100000 rows of 800K! By your result, I am > assuming that your table with the BLOB is 100 times bigger than the one > without it... I'm not sure how to get the size of a table from InnoDb, so could guess that this is true, but I'm not certain. I'm guessing that with InnoDb, the BLOB data is being stored inline with the other column data :( Thanks, Mike > -----Original Message----- > From: Benoit St-Jean [mailto:[EMAIL PROTECTED] > Sent: 12 February 2004 14:04 > To: Michael McTernan > Cc: Mysql > Subject: Re: InnoDb Table Performance problem > > > Michael McTernan wrote: > > >Hi, > > > > > > > >>SELECT COUNT(*) for InnoDB tables is a know problem... The table > >>handler (for InnoDB) has to do a table scan to count all rows... This > >>particular case is optimized with MyISAM ... > >> > >> > > > >Sure. But why is the tablescan ~100 times faster for the table > without the > >BLOB column? > > > > > Well, two possible explanations... BLOBS are either stored in a > separate area thus forcing MySQL to perform an extra seek to retrieve > the full row or the table scan takes a lot more time to execute because > of the BLOB data you have to retrieve... Reading 100000 rows of 1K is > way faster than retrieving 100000 rows of 800K! By your result, I am > assuming that your table with the BLOB is 100 times bigger than the one > without it... > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
