Cory Robin <[EMAIL PROTECTED]> wrote on 16/06/2005 08:09:22:
> I need to speed up a search, big time.
>
> I have an application that searches for records on a date field. If it
> doesn't find an exact date match, it keeps searching adjacent days until
it
> finds a certain amount of records.
>
> The problem now is, I'm using my application to loop through and run
> multiple queries and it's dog ass slow.. I'm hoping that one of you
SQL
> gurus can point me in the right direction to create a query that will
work
> it out for me. Here's the logic the best I can explain..
>
> I want to return a minimum of 15 records.. I'm searching for records on
or
> around 2005-10-01
>
> Select * from table_x where row_date = '2005-10-01'
> /* at this point if matched records are >= 15 then simply return the
records
> on that date.. If not..*/
> Select * from table_x where row_date = '2005-09-31'
>
> Select * from table_x where row_date = '2005-10-02'
>
> And so on until it finds >= 15 records or it searches through 5 days (+-
3
> on search date)
>
> I hope this makes sense.. I'm new to all this stuff.
>
> Eventually I'm going to do the same thing for times as well..
Heres a suggestion:
select * from table_x
where row_date between date_sub(now(), interval 3 day) AND
date_add(now(), interval 3 day)
order by abs(time_to_sec(datediff(created, now())))
limit 15 ;
This does times relative to now(), but I am sure you can generalise it.
The first line specifies the desired fields
The second selects (in principle) all the records within your largest
target window
The third orders them by closeness to your target time
and the last says you only want 15 of them.
This version is based on exact seconds from the target time (now() in my
case): the version which works in whole days would only be slightly
different.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
