SELECT @starttime = max(time)
There's a colon necessary in the syntax here -- SELECT @starttime := max(time)
SELECT *
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time in (@starttime, @stoptime)
... but that should be "between" instead of "in", right?
That way, if the user's name matches for both records, you are sure you have identified the correct user for a particular time
I know it appears to be a lot of steps but with a composite index on (ID,
Date, Time, RecordType) each of these queries should respond very quickly.
... and I would put the index on (IP,RecordType,Date), because I don't thing the
index on time is going to be usable.
The flaw here is that the query will fail to find spammers who dial in before
midnight on the previous day. I think the best solutions would all require
a slightly different data structure, where you somehow have a single database
record per session which records both the start time and the stop time.
(In MySQL 4.1, you could even use geometric data types, where the call as
stored as a line between the start point and the stop point, and the query
is treated as a search for lines containing a certain point.)
- JD
-- John David Duncan, Consulting Engineer MySQL AB, www.mysql.com Office: +1 415 664-7396
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
