A fast row count with DatabaseMetaData.getIndexInfo() / tableIndexStatistic / CARDINALITY?

[Daniel Tripp]

Hello all.  I am looking for a fast way to find out the number of rows in a
table.  

I am hoping for something faster than "select count(*) from X", which can
sometimes take a bit too long for me.  On one 2-million-row table of mine,
it takes 17 seconds on my machine.  Maybe I'm greedy, but I had an idea that
I could get the row count with DatabaseMetaData.getIndexInfo() instead (and
hopefully this would be faster than "select count(*) from X").  But
unfortunately the data returned by getIndexInfo() are not filling the bill.

The javadocs for java.sql.DatabaseMetaData.getIndexInfo() state:
"CARDINALITY int => When TYPE is tableIndexStatistic, then this is the
number of rows in the table...."

(http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getI
ndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean
,%20boolean))

And that is the nugget that I am after.  But Derby's
DatabaseMetaData.getIndexInfo() seems never to return a row with TYPE ==
DatabaseMetaData.tableIndexStatistic.  The following program (which produced
the same results on Derby 10.4.1.3 and 10.3.1.4) shows what I mean:

import java.sql.*;
public class GetNumberOfRowsViaMetaData {
        static public void main(String args_[]) throws Exception {
                Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
                String url =
"jdbc:derby:testDatabase-"+System.currentTimeMillis();
                Connection conn =
DriverManager.getConnection(url+";create=true");
                
                conn.prepareStatement("create table THETABLE (I integer, S
varchar(100))").execute();
                conn.prepareStatement("create index THEINDEX_ON_I on
THETABLE (I)").execute();
                conn.prepareStatement("create index THEINDEX_ON_S on
THETABLE (S)").execute();
                for(int i=0; i<42; ++i) { 
                        PreparedStatement stmt =
conn.prepareStatement("insert into THETABLE values (?, ?)");
                        stmt.setInt(1, i);
                        stmt.setString(2, ""+i);
                        stmt.execute();
                }
                
                ResultSet rset = conn.getMetaData().getIndexInfo(null, null,
"THETABLE", false, false);
                while(rset.next()) {
                        String[] columns = new String[]{"TABLE_NAME",
"INDEX_NAME", "TYPE", "CARDINALITY"};
                        for(int i=0; i<columns.length; ++i) {
                                System.out.println(columns[i]+" =
'"+rset.getString(columns[i])+"'");
                        }
                        
                        if(rset.getShort("TYPE") ==
DatabaseMetaData.tableIndexStatistic) {
                                System.out.println("--- Success ---");
                        }
                        System.out.println("---");
                }
        }
}

When I run this program, I get the following output:

TABLE_NAME = 'THETABLE'
INDEX_NAME = 'THEINDEX_ON_I'
TYPE = '3'
CARDINALITY = 'null'
---
TABLE_NAME = 'THETABLE'
INDEX_NAME = 'THEINDEX_ON_S'
TYPE = '3'
CARDINALITY = 'null'
---

Note that a TYPE of 3 is DatabaseMetaData.tableIndexOther, which is not
useful to me for my row-counting purposes.  (I need tableIndexStatistic
(0).)

Also note that "--- Success ---" is NOT printed.  When I run this same code
a Microsoft SQL Server database, though, it is printed.

I realize that the JDBC docs don't say that a row with TYPE equal to
tableIndexStatistic is /required/ to be returned by getIndexInfo().  But I
am wondering if anyone can help me out by commenting on any of this.  In
particular:

- why Derby's DatabaseMetaData.getIndexInfo() doesn't return a TYPE ==
tableIndexStatistic row 
- how Derby might be made to return one, if possible 
- any alternate way of achieving my goal of a fast row count 

Thanks in advance.