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,
Jörg
--
Joerg Bruehe, MySQL Build Team,
[EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
