count(*) vs exists()

Date:2013-02-04
Title: count(*) vs exists() Tags: Mysql
Category:It

Exists Vs. Count(*) - The battle never ends...

I am still amazed at how many of the database applications written today still disregard some basic rules of thumb when it comes to accessing the data. One in particular is the use of COUNT(*) to check to see if there are any rows that match some criteria. ——Andrew Kelly

让我们做个了结....

---

情景：

判断是否存在cat=”极客“的Article

方法一：

count = Article.objects.filter(cat="极客").count()
if count：
    # balabala...

SQL: SELECT COUNT(*) FROM table_article WHERE cat="极客";

方法二:

exist = Article.objects.filter(cat="极客").exists()
if exist:
    # balabalabala....

SQL: EXISTS (SELECT * FROM table_article WHERE cat="极客")
查看

当只有2条符合要求的数据(cat="极客")时,查询情况

• 方法一:

Scan count 1, 
logical reads 3,
physical reads 0,
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0,
lob read-ahead reads 0  

• 方法二 👍

 Scan count 1, 
logical reads 2, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

当有4688条符合要求的数据时,查询情况如下

• 方法一:

 Scan count 1, 
logical reads 11, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

• 方法二 👍

Scan count 1, 
logical reads 2, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

当没有索引,有357条符合要求的数据,总共有121317条数据时,查询情况如下

• 方法一:

 Scan count 1, 
logical reads 1241, 
physical reads 0, 
read-ahead reads 331, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.

• 方法二 👍

Scan count 1, 
logical reads 5, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 0, 
lob physical reads 0, 
lob read-ahead reads 0.