I have a table in a PostgreSQL database : temp with 4 columns
(
id int,
campaign character varying,
sender character varying,
date_s date
)
with around 9 millions records already. There is no indexes for now.
The problem is when I am trying to do a :
SELECT COUNT(*)
FROM temp
WHERE
id = $idmail and
campaign = '$variable_campaign' AND
date > '$date_c' "
in a 100K loop.
The query is not responding. (I have put a unlimited set_time_limit in PHP otherwise I'll get a 500 error under 5 minutes)
Actually the purpose of all this queries is to get a list of mails to which the concerned campaign was not sent in the current week.
Have you got any ideas please because I am really don't know how to do !
I can do a kind of temporary files if queries cannot be executed but I prefer deal with databases, it's cleaner !
Supposing there is a mails table with the id primary key:
select id
from
temp t
right join
mails m using(id)
where
t.campaign = '$variable_campaign' and
t.date > '$date_c' and
t.id is null
It will return all not sent mail ids.