I have noticed some odd behavior in my mysql server (running build 41 on Win2k). I have a large table (4 million rows, 118 columns, about 1.7Gb on disk). As its primary key, this table has an auto increment int field called 'runid'. The machine it runs on has 733Mhz cpu/1.25Gb RAM and a 60Gb ide drive (on its own controller) dedicated to mysql. While I have not set any parameters to prevent others from logging in, I am the only one logged in and running mysql client on that machine. The data in this table is completely static and I am building a number of indexes on it to speed some heavy-duty analysis I intend to perform in the near future. Enough of the background, here is the problem: When I build two indexes consecutively using mysql command line, the memory used by the first index build does not seem to be re-used by the second index build. I have all my memory set-variable quite high to take advantage of the large amount of memory on my box. If I bring up the task manager and watch the memory-utilization for my-sql, I can watch the mu creep up, bit by bit, during the first index build. Then when I start the second index build, this mu creep begins immediately as if all the memory allocated by the first index build is still spoken for. Is this behavior correct? Is there some flush command or something I can give it to avoid this? The second build also takes substantially longer (both in cpu and elapsed time) even when the specifics of the indexes are very similar (same # and type of fields, both using the primary key (int) field as the last column of the index). I believe the discrepancy may be because there is less memory available for the second index build to use for its sort. One last thing: I notice that when the index is being built, a temporary file is created in the data directory. What surprises me is that this temp file grows to about the same size as the actual table. This is probably by design but it seem inefficient in that the actual data size of the columns being indexed is obviously much smaller than that of the hole table. Why doesn't it just extract the columns it needs along with something like a unique record or page pointer and sort that? Any light that can be shed will be much appreciated - even if it just points me to some reference material that is relevant. Thanks, Will French --------------------------------------------------------------------- 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
