We are encountering two issues when using MySQL with large tables (by large,
we're talking > 1 million rows). Our application is written in Java and we
are using the mm.mysql JDBC driver. We run our applications using both
Oracle and MySQL. Below are issues that we have experienced.
1. The mm.mysql driver caches all results from a large selection in memory
before returning the ResultSet to the calling method. This means that any
select returning a lot of rows runs the risk of getting an OutOfMemoryError.
Oracle doesn't have this problem, as the ResultSet only contains some of the
rows matching the query, and more are retrieved as ResultSet.next() is
called. One of our developers has already discussed the issue with Mark
Matthews, and the impression seems to be that MySQL does not support true
cursors. Once you start a query, you must read the entire result set before
issuing any other queries. The lack of cursors seems to really limit what
types of applications can be implemented using MySQL as a backend. Are
there any discussions regarding the implementation of cursors which would
support canceling and scrolling, or is the solution to just keep adding
memory (we're running on a PII 450 w/ 128MB RAM)?
2. After we incurred problem number 1, we decided to work around it by using
the LIMIT directive and retrieving the entire table in chunks of 25000 rows
by issuing a series of queries. Unfortunately, each LIMIT query takes longer
and longer to return results, which means the overall select proceeds in
exponential time. As an example, we tried to query all of the rows for a
table containing 18 columns with 1.8 million rows (on a PII 450 w/ 128M). A
partial summary of the results is in the following table:
Selected rows Time for this select Cumulative
Time
0-24999 00:00:29 00:00:29
25000-49999 00:00:30 00:00:59
50000-74999 00:00:32 00:01:31
...
500000-524999 00:05:52 01:01:04
525000-549999 00:06:03 01:07:07
...
1000000-1024999 00:11:10 03:50:07
...
1775000-1799999 0:27:50 14:15:34
Is this phenomena expected behavior and will it be addressed sometime in the
future, or is it just the way it goes?
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this email is
intended to constitute a waiver of any privilege or the confidentiality of
this message. If you have received this email in error, please notify me
immediately by reply and delete this message.
---------------------------------------------------------------------
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
