Re: Pauses with large selects

[S Lemmon]

Sinisa Milivojevic wrote:
 
 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.

Thanks for the reply! That's good to know at least.  I really didn't
know
if anything was wrong or not.

I've been looking through the docs and the list for a few days, but it's
tricky when you don't really know what to look for (searching for
stuff like "select" and "pause" doesn't help much ;-).  However, I
think I finally stumbled upon at least part of my problem...   It's
"mysql_store_result" vs. "mysql_use_result"! 

Since both Perl's DBI and the mysql command use "mysql_store_result" by
default, all rows are buffered up before being returned - the more rows
the longer the delay. Informix behaves more like "mysql_use_result"
where results are returned as they are found.

The other problem is the delay when closing a select early.  From what I
gather it seems like it's not possible to abort a select without first
reading all the rows. I'm guessing the mysql client and Perl DBI both
hide this by just doing the equivalent of sending what remains to
/dev/null (hence the delay). 

I guess it's not really a problem now that I know what to expect.  I
just have to remember to include limits on all my selects.  It's just a
bit of a re-think since until recently, Informix had no statement like
LIMIT and it's normal to simply close the cursor (finish() under DBI)
once you've found what you're after.

Thanks,

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

Pauses with large selects

[S Lemmon]

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