Re: C api mysql_store_result vs mysql_use_result
- Original Message - From: Alex Schaft al...@quicksoftware.co.za If I were to do a select count(*) from x where y prior to doing select * from x where y to get a number of records, how would this impact performance on the server itself? Would the first query be the one to do the most processing, with the second one being faster, or would both have to do the same amount of work? Heh. The amount of work put into parsing and executing would be the same, except if you can compose your count query to use only indexed fields. Easily checked with an explain of both queries, I'd say. Also, do consider if you really need a %complete progress indicator, or if a simple record counter with no indicated endpoint will do. That is, do your users need to know how long it's going to take, or do they just want assurance that the process didn't hang? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: C api mysql_store_result vs mysql_use_result
On 2012/02/09 01:40 PM, Johan De Meersman wrote: - Original Message - From: Alex Schaftal...@quicksoftware.co.za If I were to do a select count(*) from x where y prior to doing select * from x where y to get a number of records, how would this impact performance on the server itself? Would the first query be the one to do the most processing, with the second one being faster, or would both have to do the same amount of work? Heh. The amount of work put into parsing and executing would be the same, except if you can compose your count query to use only indexed fields. Easily checked with an explain of both queries, I'd say. Also, do consider if you really need a %complete progress indicator, or if a simple record counter with no indicated endpoint will do. That is, do your users need to know how long it's going to take, or do they just want assurance that the process didn't hang? From the user's perspective, they just need to know the process didn't hang. The count() query is more for getting memory requirements upfront. Can I handle it all, or do I need to break it down into pages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: C api mysql_store_result vs mysql_use_result
- Original Message - From: Alex Schaft al...@quicksoftware.co.za From the user's perspective, they just need to know the process didn't hang. The count() query is more for getting memory requirements upfront. Can I handle it all, or do I need to break it down into pages? Then just use the cursor-based api (I guess that's mysql_use_result) all the time, and you won't have any memory problems at all. If you need to retrieve pages (as in, the third block of 10 results, for instance) LIMIT is your friend. Do read the documentation on limit, though - there's performance caveats when you use order by and similar. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
C api mysql_store_result vs mysql_use_result
Hi, I'm currently using mysql_store_result to retrieve all records of a query. This poses a problem however if say a couple of thousand records get returned, and the user gets no feedback during the progress. I now want to change this to mysql_use_result. The only catch is that you don't know how many records you're going to get and allocating memory for them. If I were to do a select count(*) from x where y prior to doing select * from x where y to get a number of records, how would this impact performance on the server itself? Would the first query be the one to do the most processing, with the second one being faster, or would both have to do the same amount of work? Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
mysql_use_result issue: useless iterating over unused results
Hello list,
I am having this issue with mysql_use_result: it seems it will iterate
over all unused results if I call 'mysql_free_result'. The following
code illustrates this:
mysql_query(mysql,SELECT * FROM a_very_large_table);
res = mysql_use_result(mysql);
i = 0;
while (row = mysql_fetch_row(res)) {
fprintf(stderr,%d,%d\n, row[0], row[1]);
if (++i 5)
break;
}
fprintf(stderr,freeing...\n);
mysql_free_result(res);
/* wait wait wait wait wait wait wait wait ... */
With 'mysql_store_results', the code would run out of memory, because
the table is very large (20m rows). But this version will run up to 2
minutes, taking all CPU cycles with it, just freeing the result.
Is this normal behaviour or can I change something for the better?
I am using mysql 4.0.22 client and server on Linux 2.6.
Thanks!
Renald
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_use_result issue: useless iterating over unused results
Hello list,
I am having this issue with mysql_use_result: it seems it will iterate
over all unused results if I call 'mysql_free_result'. The following
code illustrates this:
mysql_query(mysql,SELECT * FROM a_very_large_table);
res = mysql_use_result(mysql);
i = 0;
while (row = mysql_fetch_row(res)) {
fprintf(stderr,%d,%d\n, row[0], row[1]);
if (++i 5)
break;
}
fprintf(stderr,freeing...\n);
mysql_free_result(res);
/* wait wait wait wait wait wait wait wait ... */
With 'mysql_store_results', the code would run out of memory, because
the table is very large (20m rows). But this version will run up to 2
minutes, taking all CPU cycles with it, just freeing the result.
Is this normal behaviour or can I change something for the better?
I am using mysql 4.0.22 client and server on Linux 2.6.
Thanks!
Renald
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_use_result issue: useless iterating over unused results
Yup.
This is a normal behaviour. mysql_free_result() will
result in bringing each unasked rows to the client and
freeing it up row by row.
BTW, why do you need to do - select * from
Karam
--- Renald Buter [EMAIL PROTECTED] wrote:
Hello list,
I am having this issue with mysql_use_result: it
seems it will iterate
over all unused results if I call
'mysql_free_result'. The following
code illustrates this:
mysql_query(mysql,SELECT * FROM
a_very_large_table);
res = mysql_use_result(mysql);
i = 0;
while (row = mysql_fetch_row(res)) {
fprintf(stderr,%d,%d\n, row[0], row[1]);
if (++i 5)
break;
}
fprintf(stderr,freeing...\n);
mysql_free_result(res);
/* wait wait wait wait wait wait wait wait ... */
With 'mysql_store_results', the code would run out
of memory, because
the table is very large (20m rows). But this version
will run up to 2
minutes, taking all CPU cycles with it, just
freeing the result.
Is this normal behaviour or can I change something
for the better?
I am using mysql 4.0.22 client and server on Linux
2.6.
Thanks!
Renald
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_use_result issue: useless iterating over unused results
On 01:55 Tue 11 Jan , Karam Chand wrote: Yup. This is a normal behaviour. mysql_free_result() will result in bringing each unasked rows to the client and freeing it up row by row. This sounds to me as a useless feature. What could be the reason for such wasting behaviour? BTW, why do you need to do - select * from I need to visit every row. Originally, this came up when debugging my code, and since then I added a LIMIT statement when doing so. But it still find these kinds of things annoying and unsatisfiying from an engineering point of view --- and I was just curious if it was *me* who did something wrong. But thanks for the answer! Regards, Renald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_use_result issue: useless iterating over unused results
I am not sure why they are doing it. You need to hack the protocol to understand it. Maybe some MySQL/network GURU will be able to help yuo. Regards, Karam --- Renald Buter [EMAIL PROTECTED] wrote: On 01:55 Tue 11 Jan , Karam Chand wrote: Yup. This is a normal behaviour. mysql_free_result() will result in bringing each unasked rows to the client and freeing it up row by row. This sounds to me as a useless feature. What could be the reason for such wasting behaviour? BTW, why do you need to do - select * from I need to visit every row. Originally, this came up when debugging my code, and since then I added a LIMIT statement when doing so. But it still find these kinds of things annoying and unsatisfiying from an engineering point of view --- and I was just curious if it was *me* who did something wrong. But thanks for the answer! Regards, Renald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about mysql_use_result()
http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. BTW I tried searching for an answer in the list archives but the search there doesn't seem to work very well. I type in mysql_use_result() and get back messages that don't mention it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
At 17:26 -0700 10/6/03, Don Cohen wrote: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? Because there is no provision in the client/server protocol whereby the client can interrupt a transfer from the server that has started. In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. If you're really making the server do all the work of retrieving a million rows, especially for an interactive program, you might consider rewriting the query. For example, with LIMIT. Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
Paul DuBois writes: At 17:26 -0700 10/6/03, Don Cohen wrote: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? Because there is no provision in the client/server protocol whereby the client can interrupt a transfer from the server that has started. Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. If you're really making the server do all the work of retrieving a million rows, especially for an interactive program, you might consider rewriting the query. For example, with LIMIT. Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. The whole point here is that I *DON'T* want the server to generate all those rows. But I don't necessarily know how many I'll need at the beginning. Perhaps in some cases it will be necessary to generate all the rows in order to return the first one (for instance, if I ask for them to be sorted), but for the queries I intend to use I'd hope it would be possible to generate n tuples in time o(n). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
At 18:09 -0700 10/6/03, Don Cohen wrote: Paul DuBois writes: At 17:26 -0700 10/6/03, Don Cohen wrote: http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html says you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for. My question is: why? Because there is no provision in the client/server protocol whereby the client can interrupt a transfer from the server that has started. Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked Correct, the server doesn't send the row until you ask for it, but you cannot tell it, don't send any more. A future revision of the protocol may allow the client to cancel a transfer, but it's not possible now. for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? I'm not sure how either of those factors are relevant to the question at hand. In fact I thought that the normal interface for a database would show a screen full of results and then let you decide whether to go on to the next screen or quit. It seems crazy that if you do a query that gives a million results you have to retrieve them all. If you're really making the server do all the work of retrieving a million rows, especially for an interactive program, you might consider rewriting the query. For example, with LIMIT. Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. The whole point here is that I *DON'T* want the server to generate all those rows. But I don't necessarily know how many I'll need at the beginning. Perhaps in some cases it will be necessary to generate all the rows in order to return the first one (for instance, if I ask for them to be sorted), but for the queries I intend to use I'd hope it would be possible to generate n tuples in time o(n). You might want to use LIMIT in conjunction with SQL_CALC_FOUND_ROWS. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql_use_result()
Please reply to [EMAIL PROTECTED] (not to me personally), so that others can follow this thread. At 18:55 -0700 10/6/03, Don Cohen wrote: Paul DuBois writes: Perhaps I misunderstood the doc but I thought by using _use_result I was asking for the server not to send the next tuple until I asked Correct, the server doesn't send the row until you ask for it, but you cannot tell it, don't send any more. But before you wrote: Anyway, if you call mysql_free_result(), it will take care of retrieving and discarding and unfetched rows. If so, this does seem to be a way of telling the server you don't want any more results. No. mysql_free_result() may keep you from having to do it yourself, but the rows must still be fetched. It's just that mysql_free_result() does it for you. The real question is then whether the cost of reading n results out of m and then doing the free_result is really proportional to n or to m. It's n+m in any case. All rows must be fetched. for it. Otherwise what's all this stuff about using less space cause you only have to store one tuple at a time, and the stuff about not using this method if you might want to wait for a long time in the middle which would cause locking problems? I'm not sure how either of those factors are relevant to the question at hand. The first part (less space) would be relevant if the entire result were sent from the server and you just retrieved the rows incrementally from some buffer in the client. Even if the server internally generated the entire output at the beginning and stored them itself, but just delivered the rows incrementally as the client asked, then there would be no need to keep tables locked while there were undelivered tuples. You might want to use LIMIT in conjunction with SQL_CALC_FOUND_ROWS. I think this means that the query includes a limit. If so that requires that you know the limit before you see any results. It includes a LIMIT, but there's more to the story. Check SQL_CALC_FOUND_ROWS (and FOUND_ROWS()) in the manual. This may not suit your purposes; then again, it may. I want a way to look at the result and decide that's enough. I believe I've already indicated that you cannot. A cancel may be implemented at some point, but I don't know when. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_use_result
Greetings... When I am using mysql_use_result API to retrieve results, I am always getting 0 value for the max_length member of MYSQL_FIELD structure which is returned by mysql_fetch_fields. But when I am using mysql_store_result, max_length has correct value. Is this normal ? Is there any way to know the max_length when using mysql_use_result. Thanks in advance Insane - 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
Re: mysql_use_result
Hi, Strange...you should be able to find the number of fields from resultset with mysql_num_fields.You use MYSQL_RES like parameter for mysql_fetch_fields ? How do you use mysql_fetch_fields? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Ritesh Nadhani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 8:34 PM Subject: mysql_use_result Greetings... When I am using mysql_use_result API to retrieve results, I am always getting 0 value for the max_length member of MYSQL_FIELD structure which is returned by mysql_fetch_fields. But when I am using mysql_store_result, max_length has correct value. Is this normal ? Is there any way to know the max_length when using mysql_use_result. Thanks in advance Insane - 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 - 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
mysql_use_result, out of sync, Mysql
Hello What do to if mysql_use_result return NULL ? I can't understand ? I have only one query by connection, of course. I talk about SELECT statement in my query (not INSERT or anything else) Mysql run on linux. From time to time mysql_use_result() return NULL. I throw and catch an exception, then I try to make another query an I got 'out of sync' As mentionned in documentation, I use mysql_free_result() (even on a NULL pointer) while cathing the exception but nothing change. I have also try another mysql_use_result(), mysql_fetch_row() and mysql_free_result() in a dummy function. (by dummy, i mean nusefull) but I still got 'out of sync' Perhaps, the solution is to close and open a new connection in such a case ?. But before that, I want to understand. Thank you for your help Regards. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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
No answer for previous mail : Use of mysql_use_result in ODBC driver
Hi, I had not answer at my first mail... Sorry for posting it again but this is really important for us. -Message d'origine- De : Patrick FICHE [mailto:[EMAIL PROTECTED]] Envoy : lundi 12 fvrier 2001 18:09 : Mysql Objet : Use of mysql_use_result in ODBC driver ? Hi all, I would like to change the do_query function in execute.c module in the ODBC driver to use mysql_use_result function instead of mysql_store_result function. There is currently some comments with an eventual problem in case of SQLRowCount function. To my mind, the only thing would be that SQLRowCount could only return a result on UPDATE, INSERT and DELETE statements but not anymore on SELECT statement if you didn't retrieve all the rows. But the return value of SQLRowCount function is driver-defined for SELECT statements... So is it really a problem? Thanks for your help Patrick __ Patrick Fiche - Prologue Software (France) Software Engineer Email: [EMAIL PROTECTED] Internet : http://www.prologue-software.com ___ - 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
Use of mysql_use_result in ODBC driver ?
Hi all, I would like to change the do_query function in execute.c module in the ODBC driver to use mysql_use_result function instead of mysql_store_result function. There is currently some comments with an eventual problem in case of SQLRowCount function. To my mind, the only thing would be that SQLRowCount could only return a result on UPDATE, INSERT and DELETE statements but not anymore on SELECT statement if you didn't retrieve all the rows. But the return value of SQLRowCount function is driver-defined for SELECT statements... So is it really a problem? Thanks for your help Patrick __ Patrick Fiche - Prologue Software (France) Software Engineer Email: [EMAIL PROTECTED] Internet : http://www.prologue-software.com ___ - 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
