Hi,
My main experience is with Informix databases, but am trying MySQL on
our web server (ver 3.23.33 running on Linux 2.2.16-3). In general
it's working well, but being a MySQL newbie I'm a bit mystified by
some odd select behavior.
With Informix, doing a simple select like "select * from foo" (no where
clause or order by) will always return results immediately regardless of
how many rows are in table "foo". However, in my test MySQL database,
when "foo" is a large table I get a long pause before any results come
back and (even odder) another long pause when closing the statement
handle after fetching a few rows. MySQL's CPU usage also shoots way up
during these pauses. Are all the results are being buffered up somewhere
before anything is returned, and then, I'm guessing, purged when the
query is finished? It's odd that the closing pause is often longer and
more CPU intensive than the opening one.
With Informix, initial buffering only happens if something like a
temporary table or index has to be created. Otherwise the results are
returned as they are found by the db engine scanning through the table.
In any case, there's no delay when closing. With the MySQL database, it
seems the larger the potential result set, the longer both pauses
become. In most cases I can get around it by just trimming the results
with LIMIT, but when I actually need to iterate through a large table,
wouldn't buffering up perhaps millions of rows cause problems (if that's
what's really happening)?
Really, I'm not sure if this is a bug, some problem with my tables, or
just the way MySQL works. I'd be grateful if someone could shed some
light on what might be causing the pause, or if it's normal, perhaps a
better way to loop through all the rows in a large table without
building up a potentially huge temporary buffer somewhere.
Thanks for your time,
S. Lemmon
---------------------------------------------------------------------
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
