OK, Shawn, nice tip.
I really didn't know if it was actually performed by server or by client.
But I'll study the MySQL client protocols.
But it is still strange and needs investigation, because as I remember, I've
submit a "prove fire" to the oracle server. The prove was:
I've sent a really really really heavy query, containing several tables
cartesian joins, resulting in too many data (billions of billions of
records), and we've imaginate it could spend at least many hours. For my
surprising, the server reponse was imediate, with a only first page.
Do you think that the server continues the actual processing until the end,
despite no more pages being requested anymore?
De: SGreen@stripped [mailto:SGreen@stripped]
Enviada em: sexta-feira, 4 de novembro de 2005 02:06
Para: Fabricio Mota
Assunto: Re: RES: Delivery by Demand
Yes, it is a client-side behavior to the extent that the MySQL server does
not "page" through data. It gets the complete results unless you ask for a
LIMIT, then it stops building results after it meets the criteria of your
I really do not know much about Oracle administration and communication
protocols so I am just guessing.....I believe that even your Oracle clients
had to ask for data in "pages" instead of the full set. Are you sure your
Oracle server was really "holding" those results for you and only delivering
batches of 100 records? That seems very much like a client-side behavior
that was just hard for you to notice. It could have been designed as part of
the client library.... As I said, I just don't know but I know others on the
list have had some extensive Oracle experience. Maybe one of them can weigh
in on this....
With MySQL, the behavior you want to emulate is definitely something you
control from the client-end either by using the LIMIT clause or by pulling
down single rows in batches. You have to remember, though, that while the
client is processing it's latest "batch" of rows that the server still holds
onto a complete result set and has to maintain an open connection to your
client. It's really in the best interest of performance for your client to
spend as little time getting the data from the server. That means that you
should only write queries that ask for the data you actually need and you
should get the data out of the server as soon as possible. That way the
server has more resources available to deal with other queries.
Unimin Corporation - Spruce Pine
"Fabricio Mota" <fabricio.mota@stripped> wrote on 11/03/2005 10:52:34 PM:
> So are you telling me that it's a configuration in Client, but not in
> -----Mensagem original-----
> De: SGreen@stripped [mailto:SGreen@stripped]
> Enviada em: quarta-feira, 2 de novembro de 2005 17:37
> Para: Fabricio Mota
> Cc: mysql@stripped
> Assunto: Re: Delivery by Demand
> "Fabricio Mota" <fabricio.mota@stripped> wrote on 11/02/2005 10:23:46
> > Hi all,
> > In the past, I worked as a Oracle user. I've noted that in oracle
> > in that configuration), when we request a great amount of data, such
> > select * from million_records_table
> > It does not delivers the entire table at the first moment. It
> > little amount of data - such like a single page containing about 100
> > records - and awaits the cursor request the Record No 101 for fetch
> > set of data.
> > In MySQL - at least, with the default configuration I have used - it
> > not happens. It delivers all the million of records existent in the
> > inconditionally. I know that there is the clause LIMIT <N>, to limit
> > first N records existing in the query, but is there a way to warrant
> > "delivery by demand", such Oracle does, without to have to alter the
> > application's SQL code?
> > Thank you
> I know that in the C-API (and others) there are two commands to
> records from the server. One is mysql_store_result() which will bring
> your results into your machine in a single pull.
> The second is mysql_use_result(). That command sets up a transfer
> of pulling the rows from the server one at a time. If you need 100 rows
> data, you issue 100 mysql_fetch_row() commands. You are responsible for
> storing the records for later re-use.
> Is that the flexibility you are looking for?
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine