Help on boolean SQL query

[joey]

I have a SQL query where I get the count of something, but what I really
want is a yes/no (boolean) answer. Here is my query:

SELECT COUNT(*) AS COUNT FROM hd_entries,hier archy WHERE
IFNULL (BETWEEN_TIMES (b_yr, b_day, b_msec, e_yr, e_day, e_msec,
2001, 31, 0, 2001, 31, 63720000) >0, 1) AND
hd_entries.data _key=hierarchy. data_key AND
hierarchy.proje ct='CLUSTERII' AND
hierarchy.missi on='CLUSTER-4' AND
hierarchy.exper iment='PEACE';

Is there a way to change this such that it will only return a 0 or 1. Our
hope is that this makes the series of queries that I need to do go that much
quicker. The BETWEEN_TIMES function is a routine that I wrote that returns
a -1, 0, 1 if the entry sent in to it is between the given times.

I'm using MySQL v3.23.58 if it matters.

Thanks!
Joey

[Bill Karwin]

jo**@swri.edu wrote:

SELECT COUNT(*) AS COUNT FROM hd_entries,hier archy WHERE ...

Is there a way to change this such that it will only return a 0 or 1. Our
hope is that this makes the series of queries that I need to do go that much
quicker.

You could try fetching just one row using LIMIT. Then the query would
return an empty result set indicating a count of 0, or else a result set
of 1 row indicating a count greater than 0. This should run pretty
quick, since MySQL short-circuits when using LIMIT. Once it finds the
number of rows to satisfy the limit, it stops working on the query.

SELECT 1 AS I_FOUND_ONE
FROM hd_entries, hierarchy
WHERE ...
LIMIT 1

If you need exactly one row with a 1 or a 0, you could do the same query
as you had, but use IF() to return 1 or 0.

SELECT IF(COUNT(*) > 0, 1, 0) AS I_FOUND_ONE
FROM hd_entries, hierarchy
WHERE ...

Other solutions I could suggest involve using subqueries within the
EXISTS predicate, but you said you're using MySQL 3.23, so those
solutions won't work for you.

Regards,
Bill K.