MySQL Date Query Optimization

Question

any ideas of how to optimize the following query:

SELECT ip FROM clicks 
WHERE ip = ? 
AND SECOND(date_clicked)+4 > SECOND(NOW()) 
AND MINUTE(date_clicked) = MINUTE(NOW()) 
AND HOUR(date_clicked) = HOUR(NOW()) 
AND DATE(date_clicked) = DATE(NOW())

I mean, would be great to reduce number of DATE/TIME checks if possible. The main goal of that query is to see if click from specific IP did occur within 4 secs.

cheers, /Marcin

It not only needs optimizing, but it doesn't work at all: if you're comparing 16:05:59 with 16:06:01, it doesn't match because the minute part is different. — JJJ
– JJJ, Commented Apr 1, 2012 at 13:05
thats why there is a minute part comparison, it works 100% but can be more efficient — Marcin
– Marcin, Commented Apr 1, 2012 at 13:09
No, it doesn't. MINUTE( '16:05:59' ) != MINUTE( '16:06:01' ). — JJJ
– JJJ, Commented Apr 1, 2012 at 13:14

Michael Berkowski · Accepted Answer · 2012-04-01 13:23:23Z

3

You only need one call to DATE_ADD(), to test if the stored date in date_clicked plus 4 seconds is greater than the current timestamp (NOW()).

SELECT 
  ip 
FROM clicks
WHERE ip = ?
  AND DATE_ADD(date_clicked, INTERVAL 4 SECOND) > NOW()

Update

I would just add that in practice this isn't going to work very well. Many users share IP addresses. Under this system, two users on the same NAT address in a school or network wouldn't be able to click within 4 seconds of one another. It would be better to store some user-identifying value like a token from a session cookie or userid, than to store the IP and base the timeout on IP address.

edited Apr 1, 2012 at 13:23

answered Apr 1, 2012 at 13:04

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marcin Over a year ago

great, thats what I am talking about :-)

Marcin Over a year ago

yep, I know about it, but its to validate double-clicks only and will work well for that purpose

Collectives™ on Stack Overflow

MySQL Date Query Optimization

1 Answer 1

Update

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related