MySQL customized exists query performance-CodePudding

Because of using Hibernate, I cannot write a JPA query using exists like this (I translated it to pure SQL query):

SELECT EXISTS (SELECT * FROM account WHERE activated = true)

So I have to write an alternative query to check existance of activated accounts. After reading several suggestions on this website, I see a replacement:

select case when count(*)> 0 then true else false end from account where activated = true

My question is: Is this bad for performance, is it short circuit query. Means whether the query stops executing if it finds at least one activated account? Or it has to get all activated account then at the final, check the total result if greater than zero or not?

CodePudding user response：

(These comments are specific to MySQL with InnoDB.)

EXISTS() runs until it finds the first row that matches. COUNT() must find all the matches. So, EXISTS is at least as fast as COUNT, possibly a lot faster.

EXISTS returns true or false
a > b returns true or false
COUNT(*) returns 0 or more than 0; see below
a = true has the same effect as a

My point is: you don't need to do anything like ...then true else false...

You may see false as 0, non-zero for true. That is, the > 0 is also unnecessary (in your example).

To your specific question, the Optimizer is not smart enough to turn a COUNT(*)>0 into EXISTS.

CodePudding user response：

Your query seems reaching, why not just a simple

select count(*) 
   from account 
   where activated = true

Done, you get one record back with a count. But if you are looking just to see if ANY account is activated, all you need is

select 1
   from account 
   where activated = true
   limit 1

Here, you are forcing a limit of 1, so as soon as the first qualifying is found, the query is done, and you can proceed -- even if your database has 1000's (or even millions) of records.