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