List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:October 23 2008 9:25am
Subject:Re: Error Code 28
View as plain text  
Hi Heston, all!

Heston James - Cold Beans wrote:
> [[...]]
> One thing which is puzzling me at the moment is why its producing such large
> record sets from my query. Even when limiting the query to 700 records it
> still exceeds this 8Mb limit.
> The query is quite basic and is only returning very simple strings and
> integers, no more than maybe 8 chars long, like so:
> SELECT  bluetooth_session.bluetooth_session_id AS
> bluetooth_session_bluetooth_session_id, 
> 	bluetooth_session.result AS bluetooth_session_result, 
> 	bluetooth_session.address AS bluetooth_session_address, 
> 	bluetooth_session.message_id AS bluetooth_session_message_id, 
> 	bluetooth_session.campaign_id AS bluetooth_session_campaign_id, 
> 	bluetooth_session.created AS bluetooth_session_created, 
> 	bluetooth_session.modified AS bluetooth_session_modified 
> FROM bluetooth_session 
> WHERE bluetooth_session.created > %s 
> ORDER BY bluetooth_session.created 
> LIMIT 1, 699

The "limit" clause restricts the data returned, but not the data examined.
You ask the server to examine a certain set of rows ("where ..."),
sort it ("order by ..."),
and then to return a limited number of these rows ("limit ...").

As the data only exist in some other order and you (probably) do not
have a way to access them in the order desired (or do you have an index
on "bluetooth_session.created"?), the server must first sort all
qualifying rows. This is where it needs so much space.

Somebody else has already commented on the "limit" clause you use,
I need not repeat that.

> bluetooth_session.result and bluetooth_session.address are both 8 character
> varchars and the rest are integers and dates.
> Would you really expect a record set from this query to be so large? Is
> there any way to make it smaller and more efficient?

I strongly suspect an index on "bluetooth_session.created" would help,
both in evaluating the "where" condition and in avoiding the sort for
"order by", and so both reduce the time and avoid the temp space.

Disclaimer: Not tested, not checked - just assuming.

Regards and HTH,

Joerg Bruehe,  MySQL Build Team,
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Error Code 28Heston James - Cold Beans21 Oct
Re: Error Code 28Rob Wultsch21 Oct
  • RE: Error Code 28Heston James - Cold Beans22 Oct
    • RE: Error Code 28mos22 Oct
Re: Error Code 28Ananda Kumar22 Oct
Re: Error Code 28Joerg Bruehe23 Oct
  • RE: Error Code 28Heston James - Cold Beans23 Oct