Optimizing MySQL update query

Posted by Jernej Jerin on Stack Overflow See other posts from Stack Overflow or by Jernej Jerin
Published on 2011-01-14T20:40:50Z Indexed on 2011/01/14 20:53 UTC
Read the original article Hit count: 348

Filed under:
|
|
|

This is currently my MySQL UPDATE query, which is called from program written in Java:

String query = "UPDATE maxday SET DatePressureREL = (SELECT Date FROM ws3600 WHERE PressureREL = (SELECT MAX" +
                "(PressureREL) FROM ws3600 WHERE Date >= '" + Date + "') AND Date >= '" + Date + "' ORDER BY Date DESC LIMIT 1), " +
                "PressureREL = (SELECT PressureREL FROM ws3600 WHERE PressureREL = (SELECT MAX(PressureREL) FROM ws3600 " +
                "WHERE Date >= '" + Date + "') AND Date >= '" + Date + "' ORDER BY Date DESC LIMIT 1), ...";
        try {
            s.execute(query);
        } 
        catch (SQLException e) {
            System.out.println("SQL error");
        }
        catch(Exception e) {
            e.printStackTrace();
        }

Let me explain first, what does it do. I have two tables, first is ws3600, which holds columns (Date, PressureREL, TemperatureOUT, Dewpoint, ...). Then I have second table, called maxday, which holds columns like DatePressureREL, PressureREL, DateTemperatureOUT, TemperatureOUT,... Now as you can see from an example, I update each column, the question is, is there a faster way? I am asking this, because I am calling MAX twice, first to find the Date for that value and secondly to find the actual value. Now I know that I could write like that:

SELECT Date, PressureREL FROM ws3600 WHERE PressureREL = 
  (SELECT MAX(PressureREL) FROM ws3600 WHERE Date >= '" + 
  Date + "') AND Date >= '" + Date + "' 
ORDER BY Date DESC LIMIT 1

That way I get the Date of the max and the max value at the same time and then update with those values the data in maxday table. But the problem of this solution is, that I have to execute many queries, which as I understand takes alot more time compared to executing one long mysql query because of overhead in sending each query to the server.

If there is no better way, which solution beetwen this two should I choose. The first, which only takes one query but is very unoptimized or the second which is beter in terms of optimization, but needs alot more queries which probably means that the preformance gain is lost because of overhead in sending each query to the server?

© Stack Overflow or respective owner

Related posts about java

Related posts about mysql