[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? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
