title: Java SQL Optimization
body: <p>I am trying to use an SQL database with a Java program.  I make a table that is 7 columns wide and 2.5 million rows (My next one I need to build will be about 200 million rows). I have two problems: building the SQL table is too slow (about 2,000 rows/minute) and searching the database is too slow (I need to find over 100 million rows in under a second if possible, it currently takes over a minute).  I have tried creating a csv file and importing it, but <a href="https://stackoverflow.com/questions/10926768/sql-importing-large-csv-out-of-memory-error">I can't get it to work</a>.</p>
  
  <p>I am using xampp and phpMyAdmin on my computer (i5 + 6gb ram).  I have three methods I am testing: createTable(), writeSQL(), and searchSQL().</p>
  
  <p>createTable:</p>
  
  <pre><code>public static void createTable() {
      String driverName = "org.gjt.mm.mysql.Driver";
      Connection connection = null;
      try {
          Class.forName(driverName);
  
      } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
      String serverName = "localhost";
      String mydatabase = "PokerRanks4";
      String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
      String username = "root";
      String password = "";
  
      try {
          connection = DriverManager.getConnection(url, username, password);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
      ///////////////
      String table = "CREATE TABLE ranks(deckForm bigint(10) NOT NULL,rank0 int(2) NOT NULL,rank1 int(2) NOT NULL,rank2 int(2) NOT NULL,rank3 int(2) NOT NULL,rank4 int(2) NOT NULL,rank5 int(2) NOT NULL,PRIMARY KEY (deckForm),UNIQUE id (deckForm),KEY id_2 (deckForm))";
      try {
          Statement st = connection.createStatement();
          st.executeUpdate(table);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
      ///////////////
  
      try {
          connection.close();
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  }
  </code></pre>
  
  <p>writeSQL():</p>
  
  <pre><code>public static void writeSQL() {
      String driverName = "org.gjt.mm.mysql.Driver";
      Connection connection = null;
      try {
          Class.forName(driverName);
  
      } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
      String serverName = "localhost";
      String mydatabase = "PokerRanks4";
      String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
      String username = "root";
      String password = "";
  
      try {
          connection = DriverManager.getConnection(url, username, password);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
  
      /////////////// Prepared Statement with Batch   
      PreparedStatement statement = null;
      String sql = "INSERT INTO ranks VALUES (? ,0, 0, 0, 0, 0, 0)";      
      long start = System.currentTimeMillis();
      try {
          statement = connection.prepareStatement(sql);
          for (int i = 0; i &lt; 100; i++) {
              for (int j = 0; j &lt; 100; j++) {
                  statement.setLong(1, (i*100 + j));
                  statement.addBatch();
              }
              System.out.println(i);
              statement.executeBatch();
          }
      } catch (Exception e) {
          e.printStackTrace();
      } finally {
        if (statement != null) {
          try {
            statement.close();
          } catch (SQLException e) {
          } // nothing we can do
        }
        if (connection != null) {
          try {
            connection.close();
          } catch (SQLException e) {
          } // nothing we can do
        }       
      }
      System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
      ///////////////
  
  }
  </code></pre>
  
  <p>searchSQL():</p>
  
  <pre><code>public static void searchSQL() {
      String driverName = "org.gjt.mm.mysql.Driver";
      Connection connection = null;
      try {
          Class.forName(driverName);
  
      } catch (ClassNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
      String serverName = "localhost";
      String mydatabase = "PokerRanks2";
      String url = "jdbc:mysql://" + serverName + "/" + mydatabase;                                                                        
      String username = "root";
      String password = "";
  
      try {
          connection = DriverManager.getConnection(url, username, password);
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  
  
  
      /////////////// Option 1, Prepared Statement
      ResultSet rs = null;
      PreparedStatement pstmt = null;
      String query = "SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = ?";
      long start = System.currentTimeMillis();
      try {
          pstmt = connection.prepareStatement(query);             
          for (int i = 0; i &lt; 100000; i++) {              
              pstmt.setLong(1, 1423354957);
              rs = pstmt.executeQuery();
              while (rs.next()) {             
                  int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)};               
              }
          }
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }                       
      System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
      ///////////////
  
      /*
      /////////////// Option 2
      Statement st = null;
      long start = System.currentTimeMillis(); 
      try {
          st = connection.createStatement();
          ResultSet rs = null;
          long deckForm = 1012213456;             
          for (int i = 0; i &lt; 100000; i++) {          
              rs = st.executeQuery("SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = " + deckForm);
              while (rs.next()) {
                  int[] arr = {rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getInt(6)}; 
              }
          }
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }        
      System.out.println("Total Time: " + (System.currentTimeMillis() - start) / 1000 );
      ///////////////
      */
  
  
      try {
          connection.close();
      } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
  }
  </code></pre>
  
  <p>Sorry that's so long.  I've tried everything I can think of to make this faster but I can't figure it out.  Any suggestions?</p>