Trying to improve slow running queries

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.

Thoughts?

# Wed Jan 23 10:13:04 2013
# Query_time: 1.543565  Lock_time: 0.000131 Rows_sent: 0  Rows_examined: 392771
SET timestamp=1358961184;
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%