Re: [GENERAL] Yet Another (Simple) Case of Index not used

On Tue, Apr 08, 2003 at 05:10:01PM -0700, Denis @ Next2Me wrote:
> Interesting generic response. In other words, "it all depends".
> Well, a de facto observation is: "In my case, it's always much slower with, say, mysql".

Curious, is mysql still so fast when you have transactions enabled? How does
it deal with the following:

begin;
delete from bigtable;
select count(*) from bigtable;     -- Should return 0
abort;
select count(*) from bigtable;     -- Should give original size

> Understand me, I don't mean to be starting a performance comparaison mysql
> vs postgresql, which is probably an old subject, I am just looking for a
> solution to solve this type of performance issues, ie the generic cases:
> select count(*) from addresses where address is like 'pattern%';
> Which are very fast on mysql, and very slow on postgresql.

Ah, but that may be caused by something else altogether. LIKE is only
indexable in the C locale so if you have en_US as your locale, your LIKE
won't be indexable. See the discussion threads on this mailing list in the past.

> Understood, it will always depend on some parameters, but the real
> question is: how much control does one have over those parameters, and how
> does one tweak them to reach optimal performance?

Hmm, it depends. One person put it that mysql goes for performance first,
then correctness, whereas postgresql goes for correctness first, then
performance.

Maybe fti (full text indexing) would work better?

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington