(forwarded to the list - I am not the OP) ----- Forwarded by Shawn Green/Unimin on 06/16/2005 11:45 AM -----
James Black <[EMAIL PROTECTED]> wrote on 06/16/2005 11:44:36 AM: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > [EMAIL PROTECTED] wrote: > > [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 didn't think about the AT LEAST 15 rows. > I have two ideas: > 1) Do an order by and just fetch each day until at least 15 is reached. > 2) Two queries, the first involves a GROUP BY, 'SELECT row_date, > count(*) FROM table_x GROUP BY row_date' and see which date you need to > do the select for, then just get all the rows from that date forward. > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black [EMAIL PROTECTED] > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.1 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > iD8DBQFCsZ5kikQgpVn8xrARAh/aAJ9bp1rPVdD+oBfuc3iDourPwRFIugCfR2qF > oQvoGEWlRTpktAakMJA2Q5A= > =8bF7 > -----END PGP SIGNATURE-----
