CODEWITHSUNDEEP
mysql
ryandlf
ryandlf

Reputation: 28555

MySQL Include Count As Variable in Query

I often user a separate query to count rows to set a total variable when querying a table for paginated results.

Returns total users in table

SELECT Count(*) FROM users WHERE organizationId = :organizationId

Returns paginated results

SELECT * FROM users WHERE organizationId = :organizationId LIMIT :start, :end

public SearchResults findAll(User user, Paginator paginator) {
        HashMap<String, Object> namedParameters = new HashMap<String, Object>();
        namedParameters.put("organizationId", user.getOrganizationId());
        namedParameters.put("userId", user.getId());
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        //Get count
        int total = namedParameterJdbcTemplate.queryForInt(SQL_FIND_ALL_COUNT, namedParameters);
        int start = 0;
        int end = total;

        if(paginator != null) {         
            start = paginator.getFirst();
            end = paginator.getCount();
        }

        namedParameters.put("start", start);
        namedParameters.put("end", end);
        SqlRowSet results = namedParameterJdbcTemplate.queryForRowSet(SQL_FIND_ALL, namedParameters);
        return new SearchResults(mapUser(results), total);
    }

The only reason for collecting the total is so I can return that as part of the search results, because its necessary for pagination so I know how many pages of results to display to the user without actually having to pass the results and find every record in the database.

Is there a way to do this in one query? So in other words, get the total count, while still using the LIMIT condition to only query for a specific amount (say 10 records at a time). If so, from a performance standpoint, does it make more sense to just keep these in separate queries?

Something like this?

SELECT *, (SELECT Count(*) FROM users WHERE organizationId = :organizationId) FROM users WHERE organizationId = :organizationId"

If the above query works, how do I actually get that subquery column, does it have a name I can reference?

results.getInt("count"); //?

Upvotes: 2

Views: 5474

Answers (2)

Yogendra Singh
Yogendra Singh

Reputation: 34367

Two observations:

1> Your count query should not use '*', instead you can write a query like this:

SELECT Count(1) FROM users WHERE organizationId = :organizationId

or

SELECT Count(column1) FROM users WHERE organizationId = :organizationId

2> Using count as subquery is not good idea as it may be executed more than once and add into performance overhead. For sure, you are not gaining any thing in the performance as its still two queries.

If expected "resultset" is big, I advice to use 2 different queries. Use the count query only once in the beginning. For subsequent pages, only details query can be executed.

If expected "resultset" is small, you can load all the results and do the client side pagination for best performance. In that case, only one query is executed for all the pages.

Upvotes: 2

John Woo
John Woo

Reputation: 263733

Add an ALIAS after the subquery

SELECT  *, 
       (
        SELECT Count(*) 
        FROM users 
        WHERE organizationId = :organizationId
       ) `COUNT`
FROM users
WHERE organizationId = :organizationId

Alternative solution in your query is to use CROSS JOIN

SELECT  *, x.totalCount
FROM users, 
       (
        SELECT Count(*) totalCount
        FROM users 
        WHERE organizationId = :organizationId
       ) x
WHERE organizationId = :organizationId

Upvotes: 2

Related Questions