Long-running query against customer table
Posted: Wed Aug 03, 2011 4:43 am
From time to time we'll notice a long-running query on our database server which looks like this:
Typically this will take some minutes to complete (there's around 600,000 customers in our database).
However, if the query is written as then it completes in a fraction of a second.
As far as I can tell, the count(*) query comes from getRecipientList in RecipientDaoImpl.java - it wraps around an SQL statement generated by getSQLStatement in RecipientQueryBuilder.java.
I realise that getSQLStatement could return a query with a join table or, indeed, a query with a where clause, but for this simple case is it possible to rewrite the query so that it doesn't do a full table scan on the customer table just to get a count?
Code: Select all
SELECT count(*) FROM ( select cust.customer_id, cust.gender, cust.firstname, cust.lastname, cust.email FROM customer_1_tbl cust ) agn
However, if the query is written as
Code: Select all
SELECT count(*) FROM customer_1_tbl cust
As far as I can tell, the count(*) query comes from getRecipientList in RecipientDaoImpl.java - it wraps around an SQL statement generated by getSQLStatement in RecipientQueryBuilder.java.
I realise that getSQLStatement could return a query with a join table or, indeed, a query with a where clause, but for this simple case is it possible to rewrite the query so that it doesn't do a full table scan on the customer table just to get a count?