Hi Stephen,
----- Original Message -----
From: "Stephen Faustino" <[EMAIL PROTECTED]>
> 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.
I'll leave the Java API interface issues to the techies, but there are some
things you can do that should always be beneficial in these kinds of
situations:
> [...] 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
Additional RAM on the server is always good for performance. 128MB isn't
very much, and given the cost of RAM these days, it's a cheap path....
In this particular case, the MySQL server may not be able to keep the
entire result set in memory, so it will have to be swapped to disk. I would
guess this is at least in part responsible for the slower response times on
the later LIMIT statements, since the way you have set it up, the server has
to put together the result set first (including any ordering), swapping
parts to disk (also during ordering), and then has to pick a chunk of rows
from the middle, involving yet more swapping.
Depending on the size of your rows, adding more RAM may allow the server to
do the whole operation in memory.
Of course you will want to look at mysqld options like record_buffer,
sort_buffer and temp_table_size, as well as key_buffer size for the indexes.
See the manual for information on these and other options.
Another thing you might want to consider is first storing the large 1.8
million row result set (ordered and all) in a temporary table (if you do
this with INSERT .... SELECT or CREATE TABLE ... SELECT it can be done
without any data transfer between server and client).
Then you will be able to do very fast simple SELECT ... LIMIT queries,
particularly if you add a good (primary) index to allow the server to
process the LIMIT clause even faster.
>From a programmer's perspective, you may want to wrap all this in a simple
class that uses a select statement to create the temporary table and allow
you to pick (sets of) rows at will.
FYI, optimization techniques are an important part of MySQL training courses
(http://www.mysql.com/training/).
Regards,
Arjen.
--
Get Official MySQL Training Worldwide, http://www.mysql.com/training/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer
/_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia
<___/ www.mysql.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
