Question: I have two tables `users` and `posts` with 500k records each.

I want to find users who had written between 100 and 200 posts.

My query is:

```
SELECT u.accountid, COUNT(*)
FROM users u
JOIN posts p
ON u.accountid = p.owneruserid
GROUP BY u.accountid
HAVING COUNT(*) BETWEEN 100 AND 200;

```

And I get answer in about a second.

I added indexes on `accountid` and `owneruserid` fields in tables `users` and `posts` respectively but the query didn't speed up. Why?

Answer: 
```
HAVING COUNT(*) BETWEEN 100 AND 200;

```

The part is key to explain why indexes are futile.

We need to get only groups where member count between 100 and 200. It means for each group we need exact count of members. The second point we don't have any restrictions (e.g. WHERE section) so to get counts and all groups we need to go through all the records in the table.

Indexes e.g. B-Tree index help to find proper element (row) based on index condition. If data is somehow ordered (index provides the order) we can use binary search to find desired subset. But in our case we need to scan all records. So it does not matter whether they ordered or not.

That's why index does not speed up the query.
You can simplify the query to:

```
SELECT p.owneruserid, COUNT(*)
FROM posts p
GROUP BY p.owneruserid
HAVING COUNT(*) BETWEEN 100 AND 200;

```

The index on `posts(owneruserid)` should work for this query. It is a covering index for the query, so the query might be a wee bit faster.

Overall, the query seems to require scanning all the data in `posts` for the aggregation. The `HAVING` cannot take advantage of an index. However, the query can use the covering index to reduce I/O.