Re: Why Not MySQL?

Mitch Vincent wrote:
> 
> 7.0 :
> 
> StartTransactionCommand
> query: select * from applicants as a,applicants_states as s where
> a.firstname ~*
> '^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10 offset 0;
> ProcessQuery
> ! system usage stats:

With the numbers of records in the applicants file (and the probable
distribution of firstnames) a most efficient query under 7.0 will work
heaps better if you have that index on lower(a.firstname) and stop using
~* (i.e. just using ~).

I think this will be especially the case with your '63 fields, ~10,000
records.  I'm guessing that a significant portion of those fields are
TEXT or VARCHAR, so record size will be creeping up.  The best way to
see/show all of the information on this table is to:VACUUM VERBOSE ANALYZE applicants;
rather than to just:SELECT COUNT(*) FROM applicants;
because you/we will get to see the average record size as well.

Also, I believe I once read that putting all of the fixed length fields
at the start of the record will make for faster access, especially when
a scan is being done against those fields.  (Can someone confirm this
one? :-)

Do you keep statistics regarding what fields people actually _use_ for
their query matching?  If you can construct indexes to support those
frequent queries then you will find huge speed improvements.  These
speed improvements won't degrade as you add more records too (at least
not to the same extent).  You'll probably also find that people will use
those query terms more often once they twig to how much more quickly the
results come back to them!

Another point: 7.0 will much more frequently choose indexes when you use
the LIMIT clause, as you do.

Cheers,                Andrew McMillan.
-- 
_____________________________________________________________________           Andrew McMillan, e-mail:
Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267