0

Possible Duplicate:
optimize Query in PostgreSQL 

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
WHERE plain_contacts.has_email 
      AND NOT contacts.email_bad 
      AND NOT contacts.email_unsub 
      AND contacts_lists.list_id =67339

In here contacts_lists.contact_id and contacts_lists.list_id are indexed how to optimize this query?

Improve this question
3
  • What does EXPLAIN say? Commented Apr 19, 2011 at 8:00
  • Why did you post the same question twice? Commented Apr 19, 2011 at 8:05
  • Did you try to add an index on email_bad or email_unsub (as I suggested in the original question) Commented Apr 19, 2011 at 8:19

2 Answers 2

Reset to default
1

You probably want to index either contacts.id or plain_contacts.contact_id to speed up the join. One of the two fields should actually be a primary key.

If this is not sufficient, you will probably need to refactor the database. Why is there contacts and plain_contacts tables to begin with?

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Since you only want to inlude rows that has some flags set in the joined tables, I would move that statements into the join clause:

SELECT count(*) 
FROM contacts_lists 
     JOIN plain_contacts 
          ON contacts_lists.contact_id = plain_contacts.contact_id 
          AND NOT plain_contacts.has_email
     JOIN contacts 
          ON contacts.id = plain_contacts.contact_id 
          AND NOT contacts.email_unsub 
          AND NOT contacts.email_bad 
WHERE contacts_lists.list_id =67339

I'm not sure if this would make a great impact on performance, but worth a try. You should probably have indexes on the joined tables as well for optimal performance, like this:

plain_contacts: contact_id, has_email
contacts: id, email_unsub, email_bad
Improve this answer

Comments