query optimize

rubyonrails-talk
1

I have following query for find out the total no of counts.

2

I have following query for find out the total no of counts.

*************************************************************************** ************

  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 = 45897

*************************************************************************** ************

  It takes 150ms  to complete its task\.

Is there any another way to run the above query? Is there any way to reduce the time? is it possible? please tell me.....................................

well the first step is to run explain against the query and see what it says. Make sure you've added indexes when appropriate (at a minimum this should include the columns being joined on. You should also index columns used in the where clause that are sufficiently selective (I'd guess that contacts_lists.list_id would be a good candidate). use explain before and after to see the difference adding indexes makes to the query plan

Fred