OK, I determined that at least part of the problem was that I ran out of space in /var/tmp, so I reset TMPDIR to a directory that's 40G in size an am trying again. But I'd still be very interested in any comments on the below select statement given the size of everything involved (eg, an 1800M file output, 79,603,007 entries to be exact). I would have thought it would be best to have mysql do the sorting before writing out the file, instead of letting it write it out unsorted and using an external sort to sort later? Yes? No? Any comments at all? Hello?
Cindy writes: > >I wrote earlier: > > >select b.id, a.aid, a.wid, a.start, a.length > > into outfile 'newindex.txt' > > from wordindex a, wordforms b > > where a.wordform = b.wordform > > order by b.id, a.id, a.wid, a.start; > >I've actually sat down and tried this at the mysql command line. >Problem was, I started it at about 11pm last night and it's 1:20pm >today. Not only is it still sitting there, but it hasn't written a >single thing out yet (if I look at the outfile, it's there, but 0 >length). > >For an idea of how large the file is, when the above command is >done without the order by restrictions, I get a file of >the form > >551772 1 1 44 6 >877857 1 1 52 11 >703278 1 1 65 4 >563277 1 1 71 5 >806092 1 1 78 8 >863071 1 1 88 3 >956338 1 1 93 8 >(etc) > >that output takes about three - four hours to create, and its finished >size is > >-rw-r--r-- 1 ctmoore staff 1831793634 Jun 22 22:29 newindex.b.txt > >Is mysql unable to handle something this large when it has to sort as >well? Or are there some whizbit things I can do to fine tune this? > >Thanks, >--Cindy > >--------------------------------------------------------------------- >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 > -- Cindy [EMAIL PROTECTED] --------------------------------------------------------------------- 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
