And now for the sql question, if not done yet, you should index or reindex your table (the controlnum field is a good candidate to be in the index) and probably use some views in order to check only a part of your table, and simplify the work to perl.
Re^6: For a better efficiency and speed question!
Replies are listed 'Best First'.
I'm talking of 125 days of course, but this is not relevant here cause the "problem" is not in the data type, it is in the equation doing the comparison. Incidentally we are talking about a time here, but will be the same using any type of data that can be converted to a number, and that you can sum later to other number.
if: (X + 5) < (Y + 4) then: X + 1 < Y
And all of this leads us to this more compact and probably more desirable form of the query:
my $sql = $dbh->exec_select("SELECT acc,name,date FROM mytable WHERE
name != ''
AND CURDATE() BETWEEN date + 125 DAYS and date + 365 DAYS
AND date BETWEEN '2010-11-11' and '2011-11-11'
AND controlnum IN ('00988777','009999','12345','77766','88776','998888
order by date desc");
What is relative here is the 1 YEAR increment to the "date"
and the 240 DAYS to the CURRENT_DATE. Therefore you are subtracting 1 YEAR from 240 DAYS which is 125 DAYS that you are using to add to the "date",
which is relatively the something but less CPU processing time. I like that, I will try this and see how it will affect the performance of the query.