At 03:09 AM 6/16/05, Cory Robin wrote:
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..
Thanks in advance for any help!
Plus/minus 3 days makes a total of 7 days, not 5 days. I would try
something like:
Select * from table_x where row_date between '2005-09-28' and '2004-10-04'
order by abs(datediff('2005-10-01',rowdate) LIMIT 15;
It is not clear if you are looking for
A) one day with 15 records
B) the 15 records closest to your target date.
C) the 15 records closest to target date, plus all records for dates in
that result of 15 records.
My example query does (B). If you want (A) or (C), lease out the LIMIT
clause and do that code in your application - might also be possible with
subselects, but let's make sure that;s what you want before going
there. Knowing what version you have is crucial, because availability of
date/time functions varies widely in currently available releases.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
