Just to clarify, my problem is that I do not want MySQL to use "disk-based" temp file. Everytime I try to ORDER a result set that contains a TEXT type field, MySQL will use disk based temp file.
What can I do to: 1. SELECT a TEXT type field 2. ORDER by an INT column 3. not use a disk based temp file? This only seems to occur when the TEXT type field is greater than 255 characters. You can find the complete query below. Thanks, and sorry for the re-post. Robert Zwink http://www.zwink.net/daid.php -----Original Message----- From: Robert V. Zwink [mailto:[EMAIL PROTECTED]] Sent: Monday, April 01, 2002 11:47 AM I have a query: SELECT feature_type.title, feature.title FROM feature_build LEFT JOIN feature ON feature_build.feature_id = feature.id LEFT JOIN feature_type ON feature_build.feature_type_id = feature_type.id WHERE feature_build.product_id = '112479' AND feature_type.full_ordinal != 0 ORDER BY feature_type.full_ordinal When I include the ORDER BY the query uses a disk based temporary table. If I remove the ORDER BY the temporary table is not used. Also if I change the query to include the first 255 characters of the TEXT column: SELECT feature_type.title, LEFT(feature.title, 255) FROM feature_build LEFT JOIN feature ON feature_build.feature_id = feature.id LEFT JOIN feature_type ON feature_build.feature_type_id = feature_type.id WHERE feature_build.product_id = '112479' AND feature_type.full_ordinal != 0 ORDER BY feature_type.full_ordinal a disk based temporary table is not used. If I change the query to include the first 256 characters of the TEXT column: SELECT feature_type.title, LEFT(feature.title, 256) FROM feature_build LEFT JOIN feature ON feature_build.feature_id = feature.id LEFT JOIN feature_type ON feature_build.feature_type_id = feature_type.id WHERE feature_build.product_id = '112479' AND feature_type.full_ordinal != 0 ORDER BY feature_type.full_ordinal the disked based temporary table is used. Has anyone experienced this? If so is there any server settings I can change to allow larger text based columns? I'm not sorting by the TEXT column, I'm actually sorting by an INT column. Thanks for any help you might be able to offer. Robert V. Zwink http://www.zwink.net/daid.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
