Re: Why Not MySQL?

"Mitch Vincent" <mitch@huntsvilleal.com> writes:
>> You also need to look at how the join between a and s
>> is being done.  How big are these tables, anyway?

> ipa=> select count(app_id) from applicants_states;
>  count
> ------
> 244367

> ipa=> select count(app_id) from applicants;
> count
> -----
>  9791

Now I'm confused --- what's the data model here?  I guess each applicants
row must match many entries in applicants_states?

Anyway, I suspect you definitely want to avoid a nested-loop join :-).
It'd be fairly reasonable for the system to use either hash or merge
join, I think.  What does EXPLAIN show that the system is actually
doing with this query?

>>>> .... There are 63 fields in the 'applicants' table, all of which are
>>>> searchable. Would it be a good or bad thing to index all fields that are
>>>> searchable?
>> 
>> A lot of indexes will hurt your insert/update/delete times, so I
>> wouldn't recommend having a whole bunch of indexes unless searches are
>> far more frequent than changes.  What you want is a few well-chosen
>> indexes that match the commonly used kinds of WHERE clauses in your
>> query mix.

> It's basically a search engine so yes, searching is FAR more frequently done
> than inserts/updates/deletes

Well, there's still a cost to having a lot of seldom-used indexes,
because the planner has to sit there and consider whether to use each
one for each query.  So I'd still recommend looking at your mix of
queries and only creating indexes that match reasonably commonly-used
WHERE clauses.
        regards, tom lane