CODEWITHSUNDEEP
javamysqlsql
RTF
RTF

Reputation: 6494

MySQL select performance issue with JDBC

I'm having a performance issue with MySQL (v5.5) while running a local Java application using mysql-connector v5.1.30

I have 2 tables called A and B. I run something like this:

PreparedStatement stmtA = conn.prepareStatement("select x, y, z, u, v, w from A");
PreparedStatement stmtB = conn.prepareStatement("select val from B where something = ?");
ResultSet result = stmtA.executeQuery();
while ( resultA.next() ){
    // do something with result row
    if (some condition) { // true approx. 80% of the time
        ResultSet resultB = stmtB.executeQuery(some_value_from_resultA);
        // do something with result row  
        resultB.close();
    }
}
stmtA.close();

The problem is that this takes ~12 minutes to complete every time. Here's a few relevant bits of information:

Aside from the fact that the CPU usage of the mysql process is crazy, the reason I think there is a problem is because the application has additional functionality that runs an almost identical method against a different database table. This table has a similar number of columns to table A (also using MYISAM) but one big difference is that there is no table B here. So it works the same as above but without having to query a second table while iterating the rows of the first table.

This other table has 4,500 rows and the iteration takes less than 1 second.

So it would appear that the nested query of table B is causing the problem, but I'm not sure. I have limited experience with MySQL. If you need any more info, please ask.

Upvotes: 0

Views: 279

Answers (1)

Eran
Eran

Reputation: 393821

You might want to pre-load or cache the results of the queries you run on table B. Since table B doesn't have many records, you can afford to load it into a map.

You have two options :

  1. pre-load the entire B table and create a Map of something (key) and val (value). Then access that map instead of running 0.8*65000 queries. That would be most effective if something has many unique values and your B queries search for many of them.
  2. Store the result of each query on B in the map and check that map before running these queries. There's not point in loading the same data twice. That would be more efficient in terms of memory if you only use a small subset of the something values in your B query.

Adding an index on table B would also help.

Upvotes: 1

Related Questions