https://unsplash.com/photos/2KZfAwi-0W4

Choose Exists Rather Than Count

Scenario: Tommy just start working with senior engineer title . Tommy wants to impress rookies as soon as possible.

‘Exists’ will be the common SQL syntax ignored by people.

Advantages

If just need know whether exists data on certain condition, use exists will save lots of time. especially when table does not build index.

SQL Query Example

select exists(select * from customers WHERE city = "taipei" AND area  ="Zhongzheng");
// return 0 or 1

Laravel Example

$Customers = Customers::where(‘city’,’=’,"taipei")
->where(‘area’, ‘=’, "Zhongzheng")
->exists();
 // return true or false

Speed Testing

Supplement

Before testing SQL query, you need to check SQL cache first.

SHOW VARIABLES LIKE ‘have_query_cache’;
+------------------+-------+ | Variable_name | Value |
+------------------+-------+ | have_query_cache | YES |

you can use “SQL_NO_CACHE” like below to test query speed

select SQL_NO_CACHE select exists(select * from customers WHERE city = "taipei" AND area  ="Zhongzheng");

Tommy successfully found one , now he can make himself a cup of Cappuccino.

Reference

https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html

https://stackoverflow.com/questions/5264658/is-exists-more-efficient-than-count0