Most speed way to count rows of a join query

Question

I want to execute the following query in java, through mysql jdbc: SELECT COUNT (*) FROM ORDERS, CUSTOMER WHERE O_ORDEYKEY = O_CUSTKEY

I have this code:

Connection conn = new DatabaseConnection().createMySQLConnection();
int totalRows=0;
  for(int j=0;j<tables.size();j++)
        {
            Statement stmt = null;
            ResultSet rs = null;
            int rowCount = -1;
            try {
                conn.setAutoCommit(false);
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tables.get(j) 
 + " WHERE O_ORDERKEY = O_CUSTKEY;");
                rs.next();
                rowCount = rs.getInt(1);
                totalRows= totalRows+rowCount;
            } finally {
                rs.close();
                stmt.close();
            }

        }

But I wanted to run it faster. How can I do this ?

to add to @RiggsFolly 's comment java has support for [prepared statements](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) from atleast JDK 8 to prevent SQL injections, i suggest you into using them — Raymond Nijland, Apr 24 '19 at 12:17
*"But I wanted to run it faster. How can I do this ?"* Indexing would most likely help. — Raymond Nijland, Apr 24 '19 at 12:19
[This](https://stackoverflow.com/questions/234622/how-to-use-explain-plan-to-optimize-queries) might help - apply the same concepts to your specific database. — Andrew S, Apr 24 '19 at 12:26
To add to @RaymondNijland comment SQL has had the JOIN syntax since forever, you would also benefit from learning that as well — RiggsFolly, Apr 24 '19 at 12:28
Can you paste the table structure and the actual query that is generated? — Lajos Arpad, Apr 24 '19 at 12:41
@RaymondNijland Java has supported prepared statements since JDBC 1 (which I believe was introduced in Java 1.1), but prepared statements won't be able to help against this type of concatenation, because parameters can only be used for values, not for object names like a table name. — Mark Rotteveel, Apr 24 '19 at 13:40

score 1 · Answer 1 · edited Jun 27 '21 at 11:56

1

For easier reading you should avoid the old implicit join syntax based on where and use the SQL standard (since 1992) explicit join syntax:

SELECT COUNT (*) 
FROM ORDERS
INNER JOIN CUSTOMER  ON  ORDERS.O_ORDEYKEY = CUSTOMER.O_CUSTKEY

For better performance be sure you have an index on:

table ORDERS column O_ORDEYKEY

table CUSTOMER column O_CUSTKEY

edited Jun 27 '21 at 11:56

halfer

19,824
17
99
186

answered Apr 24 '19 at 12:50

ScaisEdge

131,976
10
91
107

1

@programmer also the newer JOIN syntax is better then the ansi join comma syntax because with the old ansi comma join syntax you can only do inner join or cross joins. the newer join SQL standard syntax has also defined left and right (right not support in all databases) joins meaning you have more options with better readablity – Raymond Nijland Apr 24 '19 at 13:06

Most speed way to count rows of a join query

1 Answers1