douji1999
2015-02-18 09:41 阅读 130
已采纳

PostgreSQL查询速度极慢

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 !

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    douxuan0698 douxuan0698 2015-02-18 10:00

    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.

    点赞 评论 复制链接分享

相关推荐