Please cc [EMAIL PROTECTED] on any response, I'm on the list but I filter to
a file, thanks.
Can someone (maybe with some MySQL-innards code experience) please explain
this problem:
Generating an index on the first character of a varchar(70) on a 350 meg
table on a PII 366 128 MB Ram, normal IDE disk takes over 17000 seconds
(ie it never even finished but the process took that much time in "show
processlist" before I finally killed it). And the server was not hung in
any way ... in fact I watched it generate a ~550Mb temporary table in the
MySQL data dir (filename began with a hash #sql). In fact the system's
resources, disk access aside, weren't even being strained ... ~8% memory,
3-4% CPU.
Obviously the limiting factor was disk access which was slowing everything
down. So here are the questions:
(1) Why does mysql do this all of this on disk -- the table (~300 MB) may
well have required more than physical memory, but the first character of
each varchar and the associated file offset of the record would only be a
fraction of that space (and isn't that all you need to create an index
... just pull out the values and the offsets and sort them and then store
in an index file).
(2) Even if the whole table needed to be manipulated and done on disk
(seems like a really poor idea) why is a temporary table much larger than
the original table being generated.
(3) Are there configuration options to somehow make MySQL behave more
intelligently than this, because this is really impractical -- 300 MB
isn't really large -- I know this system may seem like a low-end system
but its just my laptop. On our production system this takes less time,
but still much to long.
I'm not a database engineer, in fact I'm very new to databases, and I
don't mean to sound hostile (I know I may come off that way), but I'm just
a little suprised. I'm guessing there is a way around this behaviour
(MySQL is usually used with large DB's ... I can't imagine this would have
been ignored), but I am a little suprised that the default configuration
is _so_ badly tuned to large databases.
I would appreciate any help and explanations (for my own benefaction,
in fact the more you are willing to explain and DB's and their innards
the happier I'd be) -- the more detailed the better.
Thanks in advance,
Sheer
---------------------------------------------------------------------
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
