Mysql – Best way to check if record exists or not in MySQL

MySQL

I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.

For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .

Query goes something like this inside procedure

set @cnt = (select count(*) from table where date_field between date1 and date2) ; 
if @cnt > 0 then 
    select * from table where date_field between date1 and date2 ; 
else 
    select * from table order by date_field desc limit 0,20 ; 
end if ;

Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.

Best Answer

You could probably reduce this to 2 queries with something like:

select * from table where date_field between date1 and date2 ; 
set @count = found_rows()
if @count = 0 then
    select * from table order by date_field desc limit 0,20 ; 
end if ;

Related Solutions

MySQL – How to Retrieve Rows of Different Dates Using IN Clause

If you are doing a range of consecutive dates, there is no need to use the IN operator.

If you use this to compute midnight seven days ago

DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND

here is what you get back:

mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND |
+--------------------------------------------------+
| 2012-11-21 00:00:00                              |
+--------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Your query will look like this:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND );

You also need an index to support the WHERE and COUNT

ALTER TABLE tracks ADD INDEX track_time_user_ndx (track_time,user);

Then, the query should go faster.

If you are doing a range of consecutive dates from some earlier period, simply supply the first and last date from midnight to midnight. For example, for the range 2012-11-12 to 2012-11-19:

SELECT COUNT(DISTINCT `user`) FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <  '2012-11-19 00:00:00';

If you are doing a range of non-consecutive dates, simply structure the query using UNION. For example, for dates 2012-11-12,2012-11-19,2012-11-26:

SELECT COUNT(DISTINCT `user`) FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <= '2012-11-12 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-19 00:00:00'
    AND   `track_time` <= '2012-11-19 23:59:59'
    UNION
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-26 00:00:00'
    AND   `track_time` <= '2012-11-26 23:59:59'
) A;

Give it a Try !!!

UPDATE 2012-11-27 13:52

If you want count of distinct users for just one date 2012-11-12

SELECT COUNT(DISTINCT user) usercount FROM `tracks`
WHERE `track_time` >= '2012-11-12 00:00:00'
AND   `track_time` <= '2012-11-12 23:59:59';

If you want count of distinct users for 7 consecutive dates 2012-11-12-2012-11-18 (You cannot include 2012-11-19)

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= '2012-11-12 00:00:00'
    AND   `track_time` <  '2012-11-19 00:00:00'
) A GROUP BY DATE(track_time);

UPDATE 2012-11-27 14:41 EDT

If you want this dynamically done as of NOW(), try this:

SELECT
    DATE(track_time) track_date,
    COUNT(DISTINCT user) usercount
FROM
(
    SELECT user,track_time FROM `tracks`
    WHERE `track_time` >= ( DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND )
) A GROUP BY DATE(track_time);

MySQL – Optimized Query to Fetch a Record from 10 Million Entries

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE );

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= ( NOW() - INTERVAL 300 SECOND );

What does the value indicate ???

If you get 0, the UniqueKey is older that 5 minutes
If you get 1, the UniqueKey is within the 5 minutes

Make sure the UniqueKey has a unique index.

Give it a Try !!!

Since your timestamp is a UNIX timestamp, I'll adjust the code using UNIX_TIMESTAMP() function

SELECT COUNT(1) FROM mydb.mytable
WHERE UniqueKey = ????
AND timeStamp >= UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE);

Best Answer

Related Solutions

MySQL – How to Retrieve Rows of Different Dates Using IN Clause

UPDATE 2012-11-27 13:52

UPDATE 2012-11-27 14:41 EDT

MySQL – Optimized Query to Fetch a Record from 10 Million Entries

Give it a Try !!!

Related Question