[EMAIL PROTECTED] wrote on 16/06/2005 16:29:46:
>
>
> [EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM:
>
> > 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
>
> Only one problem with your solution, LIMIT tells how many records AT
> MOST to return, he wants to get 15 AT LEAST and stop appending
> records once he gets over 15 total results.
>
> I can't seem to make a query (in reply to his problem) to return AT
> LEAST 15 rows without some sort of iteration or flow control
> involved in the process. Neither on of which is available in MySQL
> SQL until 5.0+. Since he didn't say which version he is using I am
> assuming a target version of 4.1 or less for the solution. Does
> anyone else have a non-scripted solution?
That wasn't the
way I read it "And so on until it finds >= 15 records or it searches
through 5 days" - within I interpret as wanting all the records within 5
days up to a limit of 15. I presume that if >15 records are found, those
closes to the target time are preferred.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
