You may have nailed it. Everythig would have been indexed I. The order
it was grouped by, so perhaps the order in which things are indexed
and accesse is the kicker, or perhaps we've been consistantly lucky.
We also weren't adding image data to blobs, we were bit mapping
faceted data to blob and shifting to allow people to shop by artist or
color or subject matter across millions of posters. Normalized tables
just weren't cutting it, and bit shifting up to 32 bit was crazy fast.
After we rolled it out in production, we found mysql converts blobs to
32 bit unsigned ints before shifting. Postgres appears to not do this
at all, or our arbitrarily large test data did not trigger it on
postgres.
After the last few days, it is becoming apparent how much of a joke
mysql has been. Thanks again for such quick direction!
Corey Tisdale
On Oct 3, 2009, at 5:53 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Corey Tisdale <corey@xxxxxxxxxxxxxxxx> writes:
We're coming from mysql 4, and changing the sort order changes the
values of all columns as you would expect, given that you would
expect
a sort statement to affect grouping. This certainly isn't the only
time I've used this syntax. I've been mysql user for ten years, and
the outcome has been consistant across hundreds of tables and
millions
of rows and thousands of queries. If you ever have to use or modify a
mysql db, just keep this in mind in case it saves you some time.
Okay, I got sufficiently interested to drag out the nearest copy of
mysql and try it ...
mysql> create table t (f1 int, f2 int, f3 int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(1,11,111), (1,22,222), (1,44,444),
(1,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t values(2,55,555), (2,22,222), (2,44,444),
(2,33,333);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t values(3,55,555), (3,22,222), (3,44,444),
(3,77,777);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t group by f1 order by f2;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 1 | 11 | 111 |
| 2 | 55 | 555 |
| 3 | 55 | 555 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> select * from t group by f1 order by f2 desc;
+------+------+------+
| f1 | f2 | f3 |
+------+------+------+
| 2 | 55 | 555 |
| 3 | 55 | 555 |
| 1 | 11 | 111 |
+------+------+------+
3 rows in set (0.00 sec)
Looks to me like we're arbitrarily getting the physically-first row in
each f1 group. It's certainly not looking for the minimum or
maximum f2.
The above is with 5.1.37, but I find essentially the same wording in
the 3.x/4.x manual as in the 5.1 manual.
Now it's certainly possible that in particular circumstances you might
happen to get the right results --- for example, a scan that was using
an index might happen to deliver the rows in the right order. But I
don't see any evidence that mysql is reliably producing groupwise
minimums or maximums with this syntax. The long discussions in the
comments here:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
don't suggest that anyone else believes it works, either.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
