CODEWITHSUNDEEP
sqlindexing
Budda
Budda

Reputation: 18353

Why select of count fetches a lot of rows?

Table teams contains 1169 rows, 1133 from them have UserId field !=0. There is an index on the "UserId" field

Query:

EXPLAIN SELECT count(*) FROM teams WHERE UserId != 0

returns output that has Estimate of rows to be examined equal to 1133.

Why query need to examine all rows? Should not it just use index for this purpose?

Thank you.

Upvotes: 0

Views: 57

Answers (1)

Alejandro B.
Alejandro B.

Reputation: 5092

It will examine almost all rows because you want almost all rows (because you said UserId != 0). Sure, you then make a "count" so you show only one record, but they all had to be fetched in order to count them. If you where to do 

select count(1) from teams where UserId = 100

then it will examine ony a few rows, because you are asking for a precise value (UserId = XX as opposed to UserId != yy).

Upvotes: 1

Related Questions