I didn't think the following query was overly complex, but apparently it is, as I found this in the slow queries log on my web server.
Wed Jan 23 10:13:04 2013
Query_time: 1.543565 Lock_time: 0.000131 Rows_sent: 0 Rows_examined: 392771
SELECT loginID FROM logins WHERE uID = '12392' and loginDate like '2013-01-23%'
1.5 seconds for 390 thousand rows isn't bad.
Is the uID field a numeric or character. If numeric, take out the quotes. Is the loginDate a date field? If so, you could try loginDate BETWEEN '2013-01-23 00:00:00' AND '2013-01-23 11:59:59'. The conversions can give you a hit on performance.
Try using the Explain syntax before your query to try and understand what is happening - http://dev.mysql.com/doc/refman/5.0/en/explain.html
EXPLAIN SELECT loginID FROM logins WHERE uID = '12392' and loginDate like '2013-01-23%
This topic is now closed. New replies are no longer allowed.