We're using PostgreSQL v8.2.3.
There are tables involved: EMPLOYEE and EMAILLIST.
Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email)
2 tables are joined in such a way that if either EMPLOYEE.EMAIL1 or EMPLOYEE.EMAIL2 do not have a matching entry, those rows will be returned.
SELECT employee.email1, employee.email2, e1.email IS NOT NULL AS email1_matched, e2.email IS NOT NULL AS email2_matched FROM employee LEFT JOIN emaillist e1 ON e1.email = employee.email1 LEFT JOIN emaillist e2 ON e2.email = employee.email2 WHERE e1.email IS NULL OR e2.email IS NULL
EMAILLIST table is indexed. Now, the response time is 14 seconds.
Table count statistics: Currently, EMPLOYEE has got 165,018 records & EMAILLIST has got 1,810,228 records, and both tables are expected to grow in future.
- Is it a good idea/approach to index a VARCHAR column? This question immediately strike on my mind because of the reason that we've not indexed a VARCHAR column before in our application. Experts advice/suggestion on this are highly appreciated.
- With this current query and index, the response time of 14 seconds is reasonable or is there any scope for further tuning? What are other user's real-time experience/opinion based on this kind of table size and response time?
NOTE: My actual requirement/use case is explained in detail here.