S Lemmon writes:
> 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
>
Hi!
With :
select * from table;
queries MySQL returns results immediately.
Any problem that you experience might come only from poor build, some
OS bug or some wrong settings.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
