Java MySQL Programming

Question

I am accessing a MySQL table that has over 1 million or more Records. I am using My SQL query browser which is unable to grab all the records and it break the connection in the middle. Now I have to write a Java Program which access that particular table without being broken in the middle as this table will be modified and accessed frequently.

Can you experts suggest me how should do I go over this problem

either I create an Index on the table and how do I create index

Answer 1

I am not very well used to MySQL programming, but generally indexes are used to arrange the values of one or more columns in a database table in specific order.

SYNTAX

CREATE INDEX IndexName ON tableName (column);

Just go through this tutorial for more information,

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Answer 2

There are different reasons why a MySQL connection might break during a query. Can you give the exact error message you receive?

A simplified explanation on how to add an index to the table for a simple query

1. Look at the field(s) in the WHERE clause of the query
2. Add an index on the field(s) using ALTER TABLE ADD INDEX
3. Use EXPLAIN on the query and check if the query is actually using the index.

IF you want more specific help, Post the SHOW CREATE TABLE and the EXPLAIN of your query.

Answer 3

MySQL query browser limits the number of records to be displayed for performance reasons, because it is an interactive program and nobody like to wait for half an hour before the program crashes with an out-of-memory error. You can change these limits in the settings.

Your Java program will face similar problems.

When using large datasets it is important to plan how you are going to access that dataset and create the necessary indexes.

It would be useful to edit the question to show the structure of the data. Generqlly it looks like this :

CREATE INDEX idx_customer_name ON customer (name);

Here are more details

If you just want to dump the data to work on the data using Excel you can try this on the commandline

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=\" --fields-terminated-by=,

In my experience this is a very painful exercise as Excel really is not made to deal with this amount of rows, and the dump format usually is slightly, but infuriatingly incompatible.

Your best bet is to invest an hour of your time to go through a SQL tutorial like sql fundamentals and play with MySQL query browser to get a feel of what you can do with SQL. I guarantee your investment paid itself back by tomorrow.