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

Denis,

> Are you saying the 7.4 'group by' trick would be faster than the simple
> select count(*)? That seems hard to believe, being that the request now has
> to fetch / sort the data. I must be missing something.

No, I'm saying that the 7.4 hash-aggregate is faster than the same query was
under 7.2 or 7.3.   Much faster.   But it does little to speed up a raw
count(*).

> The kind of requests that I am really interested in are:
> select count(*) from table where table.column like 'pattern%'

Hash-aggregates may, in fact, help with this.   Care to try downloading the
the source from CVS?

> These seems to go much master on mysql (which I guess it not a MVCC
> database? or wasn't the Innobase supposed to make it so?),

They did incorporate a lot of MVCC logic into InnoDB tables, yes.  Which means
that if SELECT count(*) on an InnoDB table is just as fast as a MyISAM table,
then it is not accurate.  This would be in keeping with MySQL's design
philosophy, which values performance and simplicity over accuracy and
precision -- the opposite of our philosophy.

> So, in the meantime, I've decided to split up my data into two sets,
> the static big tables which are handled by mysql, and the rest of it
> handled by postgresql....

Hey, if it works for you, it's probably easier than dealing with the
PostgreSQL workarounds to this performance issue.  I'll ask you to give
PostgreSQL a try for those tables again when 7.4 comes out.

> ps: apologies for the cross-posting.

De nada.   The Performance list is the right place for this sort of question
in the future.

--
Josh Berkus
Aglio Database Solutions
San Francisco