# Fastest way to count records returned by a query

**URL:** <https://discuss.aerospike.com/t/fastest-way-to-count-records-returned-by-a-query/2379>\
**Category:** General Discussion\
**Tags:** query, aggregation\
**Created:** [January 12, 2016, 5:58pm UTC](https://discuss.aerospike.com/t/fastest-way-to-count-records-returned-by-a-query/2379 "2016-01-12T17:58:53Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![rbotzer](https://sea1.discourse-cdn.com/flex019/user_avatar/discuss.aerospike.com/rbotzer/32/2114_2.png) [@rbotzer](https://discuss.aerospike.com/u/rbotzer)\
**Post date:** [January 12, 2016, 6:42pm UTC](https://discuss.aerospike.com/t/fastest-way-to-count-records-returned-by-a-query/2379/4 "2016-01-12T18:42:14Z")

</div>

How would it be impossible? Let’s take an RDBMS for comparison - if the number of records in a query is changing rapidly, then the count is also changing with it. Each time you query it would be different. No difference there. A COUNT() aggregation function is executed server-side as well.

In the case of the secondary index we don’t yet have native aggregation functions, so you need to implement it via a stream UDF. For your case you don’t need a filter at all. You give it the BETWEEN predicate, and the records matched by it in the secondary index stream through the UDF. All you need to do is have a simple mapper that returns 1, and a reducer to further sum it up.

I have a set `sp` with a bin `i`, and a secondary index over it.

```sql
CREATE INDEX test_sp_i_idx ON test.sp(i) NUMERIC

```

I inserted 100 records with consecutive values for `i`.

The stream UDF is in a module named `aggr.lua`

```nohighlight
local function counter(record)
  return 1
end

local function sum(v1, v2)
  if type(v1) == 'number' and type(v2) == 'number' then
    return v1 + v2
  else
    return 0
  end
end

function count(stream, password)
  return stream : map(counter) : reduce(sum)
end

```

I’ll use a simple Python script to call it

```python
from __future__ import print_function
import aerospike
from aerospike import predicates as p

client = aerospike.client({ 'hosts': [('192.168.119.3', 3000)]}).connect()
#client.udf_put('aggr.lua')

s = client.query('test','sp')
s.where(p.between('i', 1, 50))
s.apply('aggr', 'count', [])
r = s.results()
print(r)

```

The result is

```auto
[50]

```

---

_[View the full topic](https://discuss.aerospike.com/t/fastest-way-to-count-records-returned-by-a-query/2379)._