You're absolutely right about the := not just = (Sorry!!)
Actually, I DID want to use an "IN" and not a "BETWEEN". What I was doing
was picking the two boundary times around a given point in time so once I
got those numbers, I wanted to get both records. I could have written the
WHERE like this:
WHERE [EMAIL PROTECTED] OR [EMAIL PROTECTED]
Excellent catch for the logic failure of not detecting a starttime before
midnight.
I also just realized that instead of trying to make *date* matches in the
final SELECT, I should have collected the row IDs in the first two
queries. That would change the whole group to look like this (and I added
some parametric variables to make it more generic):
SET @targetIP ='66.50.xxX.245', @targetDate = '2004-07-05', @targetTime =
'16:15:00';
SELECT @starttime := max(ID)
FROM logtable
WHERE IP = @targetIP
AND (
(Date= @targetDate AND Time <[EMAIL PROTECTED])
OR (Date < @targetDate AND Date > (@targetDate - interval
1 week))
)
AND RecordType = 'Start';
SELECT @endtime := min(ID)
FROM logtable
WHERE IP = @targetIP
AND ID > @starttime
AND Date <= (@targetDate + interval 1 week)
AND RecordType = 'Stop';
SELECT *
FROM logtable
WHERE ID in (@starttime, @stoptime)
I limited the search for a Start or a Stop record to +/- 1 week from the
target date. Depending on the actual usage patterns, that range may need
to change.
I also suggested earlier today (off list) that the original poster try to
combine his separate date and time columns to a single column so that a
true index date search would be possible.
John, thank you.
Humbly,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
John David Duncan <[EMAIL PROTECTED]> wrote on 07/22/2004 02:50:44 PM:
> The "three query solution" looks like a good one; but here are some
> notes...
>
> > 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]
>
