7 messages in com.mysql.lists.javaRe: Order by very slow
FromSent OnAttachments
Jim Tyrrell11 Jan 2003 18:16 
Christopher Taylor11 Jan 2003 20:29 
Jonathan Baxter11 Jan 2003 20:48 
Jim Tyrrell11 Jan 2003 21:28 
Willie Klein16 Jan 2003 08:06 
Christopher Taylor16 Jan 2003 10:03 
Jim Tyrrell17 Jan 2003 07:54 
Subject:Re: Order by very slow
From:Jim Tyrrell (jimt@yahoo.com)
Date:01/17/2003 07:54:19 AM
List:com.mysql.lists.java

Willie et al,

I redesigned my tables to have a blob table and data table. The performance of MySQL now is really fast. Somehow disk reads or something was getting in the way of order bys with blob data being in the same table.

Hopefully this helps you with what you should do.

Thank you Jim Tyrrell

--- Willie Klein <wil@pdfsystems.com> wrote:

Hi All;

Since I've been developing an application with Blobs and data I've been following this thread with interest. My tables haven't grown to 1 GB yet so I'd like to redesign my tables now while in the development rather than later when real data is in them.

My question is this, when you are doing your Order By is the Order By field an index field? If it is I'm not sure why the sort would take so long, MySQL shouldn't be looking at the blobs at that point right? Or does MySQL just have difficulty sorting a table with blobs?

Thanks to all, I learn a lot lurking on this list.

willie

-- Original Message ----- From: "Jim Tyrrell" <jimt@yahoo.com> To: <ja@lists.mysql.com> Sent: Sunday, January 12, 2003 12:28 AM Subject: Re: Order by very slow

Chris et al,

This is what I did after thing about it. My data is being reloaded and it appears that it is a lot faster. Word to the wise Blob data should be in a seperate table from your data that describes it.

It appears that my tables are several times faster even with order by's.

Thanks to all.

Jim Tyrrell --- Christopher Taylor <csta@nanshu.com>

wrote:

I'm not a MySQL expert, but when I put blobs in the database, I usually put them in a separate table from the metadata:

So, if you broke your photo table into two tables:

photo_meta_data: PhotoID, PhotoName, Caption, image_id

photo_image_data: image_id, image

I'm pretty sure it would run much faster.

-Chris

----- Original Message ----- From: "Jim Tyrrell" <jimt@yahoo.com> To: <ja@lists.mysql.com> Sent: Sunday, January 12, 2003 11:16 AM Subject: Order by very slow

Everyone,

I have spent the last 3 or 4 hours looking

into

this

and have not found a good solution and am wondering if anyone else has seen this.

I have a table PhotoID (PK), PhotoName

char(40),

image

MediumBlob(), Caption char(100) and a few other columns. The database is like 4 Gig. and runs great expect. I know someone might say why put the images in the database, but of any solution I have

tried

I

like this the best and is easy.

I run "select caption from Photos where

PhotoName

like

'%bob%';" this returns in .08 sec.

Now the problem when I run : "select caption from Photos where PhotoName like '%bob%' order by PhotoID;" It takes minutes to return.

I looked at explain and a few other things but nothing pointed to a problem. It looks to me like

Order

by is

being very expensive for some reason. I also wrote a query like Select Caption from Photos where PhotoID in (A long list) when this just ran it was like

.09

secs

when I added in a order by it was like 3.0 secs.

Is there a bug in Order by? Should I upgrade to the latest I am using 4.0.5a-beta?

Has someone else seen this. I checked the

news

groups

and could not find a thing.

I turned up my parameters of: set-variable = sort_buffer_size=32M set-variable = read_rnd_buffer_size=16M set-variable = read_buffer_size=16M set-variable = table_cache=256 set-variable = key_buffer_size=32M with no effect.

Someone please enlighten me.

Thank You Jim Tyrrell

__________________________________________________

Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign

up

now.

http://mailplus.yahoo.com

---------------------------------------------------------------------

Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <java@lists.mysql.com> To unsubscribe, e-mail

<java-unsubscribe-cstaylor=nans@lists.mysql.com>

__________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

---------------------------------------------------------------------

Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <java@lists.mysql.com> To unsubscribe, e-mail

<java-unsubscribe-willie=pdfs@lists.mysql.com>

---------------------------------------------------------------------

Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <java@lists.mysql.com> To unsubscribe, e-mail

<java-unsubscribe-jimtyrrell=yaho@lists.mysql.com>

=== message truncated ===

__________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com