Thread: Re: [QUESTIONS] MySQL benchmark page

Nice to see the MySQL crashme page has updated to its PostgreSQL title
to PostgreSQL 6.3, but it seems nonee of the items haven't been updated
for this release, http://www.tcx.se/crash-me.html.

I see no mention of the new subselects, and it says we don't support
comments as --.  And I see an item called "atomic updates" which I think
we decided was a very bizarre test, but still no mention of
transactions, which is a very basic database capability.  We also have
large objects.  Is that the same as blobs?  We have "Function
concatenation with ||".  We have "Function CURRENT_DATE".  We have
"Function LEFT".  We have "Function IN on strings in SELECT".  We have
"Function = ALL".  We have "Update with sub select".  We have "case
insensitive compare".  We have "lock table".

Many of these we have had for over a year.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On 15 Mar 98 at 22:31, Bruce Momjian wrote:

> Nice to see the MySQL crashme page has updated to its PostgreSQL title
> to PostgreSQL 6.3, but it seems nonee of the items haven't been updated
> for this release, http://www.tcx.se/crash-me.html.
>
> I see no mention of the new subselects, and it says we don't support
> comments as --.  And I see an item called "atomic updates" which I think
> we decided was a very bizarre test, but still no mention of
> transactions, which is a very basic database capability.  We also have
> large objects.  Is that the same as blobs?  We have "Function
> concatenation with ||".  We have "Function CURRENT_DATE".  We have
> "Function LEFT".  We have "Function IN on strings in SELECT".  We have
> "Function = ALL".  We have "Update with sub select".  We have "case
> insensitive compare".  We have "lock table".
>

I have looked into crash-me and screened the items you mentioned.
Here is the result:

about the large objects. I looked in the manual of postgres and
couldn't find that fast anything about large objects.
blobs is a type where you can put binary or other big things in.

first the concatenation with ||
the test is select 'abc' || 'def'
and that's not working in postgres. In other db's it's working.

function left:
the test is select left('abcd',2)
looked in the manual of postgres and couldn't find the function left.
this isn't working and I tried some other ways but couldn't find any
working query.

function current_date:
the test is select current_date()
that's postgres not supporting only select current_date (without ()).
that's also the case with current_time, current_timestamp. I will make
patch so it's tested both ways.

Function IN on strings in SELECT:
the test is: select 'monty' in ('david','monty','allan')
and that's not working. it gives: using an explicit cast

Function = ALL:
query: select a from crash_me where b =all (select b from crash_me)
yes
so it's supported

Update with sub select:
query: create table crash_q (a integer,b char(10))
query: insert into crash_q values(1,'c')
query: update crash_q set crash_q.b= (select b from crash_me
where crash_q.a = crash_me.a)
Got error from query: 'update crash_q
set crash_q.b= (select b from crash_me whe re crash_q.a = crash_me.a)'
ERROR:  parser: parse error at or near "=" query: drop table crash_q
no
I think this test shows that it isn't possible todo an update with a
sub select

case insensitive compare:
query: select b from crash_me where b = 'A'
query: select b from crash_me where b = 'A' didn't return any
result
no
(field b in table crash_me has a value of 'a'.)

the transaction are still not inserted in the crash-me test because
monty and I didn't have time to insert it. But what I mentioned
before if you would like to see some other tests in the crash-me test
you can add them by your self and send us the patch. We will add it
to the release of crash-me.

I am not sure the results of postgres in the crash-me page is from
the final release of postgres. Because I have so much problems with
postgres it's at this moment hard to test. Further it's taking a very
lot of time to test the postgres db because it's eating my memory.
When I test the limit of the number of joins I can do it's eating my
memory till there is no memory left anymore. And that's a lot (96 mb
real + 470mb swap).

Greetz...

Luuk
|-----------------------------------------------------------------------|
|  Luuk de Boer -> <luuk@pi.net>                                      |
|  When you want to go fast .....                                       |
|  Drive a cobra on the road and take a T3 line for internet ...... :-) |
|  http://www.luuk.com/  -> The AC Cobra site                           |
|-----------------------------------------------------------------------|

> I have looked into crash-me and screened the items you mentioned.
> Here is the result:
>
> about the large objects. I looked in the manual of postgres and
> couldn't find that fast anything about large objects.
> blobs is a type where you can put binary or other big things in.

We call them large objects.

>
> first the concatenation with ||
> the test is select 'abc' || 'def'
> and that's not working in postgres. In other db's it's working.

Looks like we have a type problem here.  That type system again.

>
> function left:
> the test is select left('abcd',2)
> looked in the manual of postgres and couldn't find the function left.
> this isn't working and I tried some other ways but couldn't find any
> working query.

Yep, we don't have it.  We have lots of them in the oracle_compat
module, but not that one.

> function current_date:
> the test is select current_date()
> that's postgres not supporting only select current_date (without ()).
> that's also the case with current_time, current_timestamp. I will make
> patch so it's tested both ways.

Does the standard say we need those parens?

> Function IN on strings in SELECT:
> the test is: select 'monty' in ('david','monty','allan')
> and that's not working. it gives: using an explicit cast

Darn type system again.

> Function = ALL:
> query: select a from crash_me where b =all (select b from crash_me)
> yes
> so it's supported

Cool.

>
> Update with sub select:
> query: create table crash_q (a integer,b char(10))
> query: insert into crash_q values(1,'c')
> query: update crash_q set crash_q.b= (select b from crash_me
> where crash_q.a = crash_me.a)
> Got error from query: 'update crash_q
> set crash_q.b= (select b from crash_me whe re crash_q.a = crash_me.a)'
> ERROR:  parser: parse error at or near "=" query: drop table crash_q
> no
> I think this test shows that it isn't possible todo an update with a
> sub select

Yes, we do not support subqueries in the target list.  We do support
subqueries in the WHERE clause of all queries, including UPDATE, and I
mistakenly thought that was the item.   Adding this feature is on our
TODO list, because subqueries legally should be allowed in any target
list, including UPDATE and SELECT.

>
> case insensitive compare:
> query: select b from crash_me where b = 'A'
> query: select b from crash_me where b = 'A' didn't return any
> result
> no
> (field b in table crash_me has a value of 'a'.)

We use ~* for this.  How do you control case-sensitivity?  Are all
strings compared for lower and upper case.  We also have many of those
math functions, but they are operations, not names like exp().  Try the
new psql \df and \do commands.

Also, it mentioned that MySQL ignores the CREATE INDEX operation.  What
does that mean?  Does MySQL only support one index on a table?

> the transaction are still not inserted in the crash-me test because
> monty and I didn't have time to insert it. But what I mentioned
> before if you would like to see some other tests in the crash-me test
> you can add them by your self and send us the patch. We will add it
> to the release of crash-me.
>
> I am not sure the results of postgres in the crash-me page is from
> the final release of postgres. Because I have so much problems with
> postgres it's at this moment hard to test. Further it's taking a very
> lot of time to test the postgres db because it's eating my memory.
> When I test the limit of the number of joins I can do it's eating my
> memory till there is no memory left anymore. And that's a lot (96 mb
> real + 470mb swap).
>
> Greetz...
>
> Luuk
> |-----------------------------------------------------------------------|
> |  Luuk de Boer -> <luuk@pi.net>                                      |
> |  When you want to go fast .....                                       |
> |  Drive a cobra on the road and take a T3 line for internet ...... :-) |
> |  http://www.luuk.com/  -> The AC Cobra site                           |
> |-----------------------------------------------------------------------|
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> > first the concatenation with ||
> > the test is select 'abc' || 'def'
> > and that's not working in postgres. In other db's it's working.
>
> Looks like we have a type problem here.  That type system again.

"that type system" is A Good Thing(tm).

tgl=> select 'abc' || 'def';
ERROR:  There is more than one operator '||'
 for types 'unknown' and 'unknown'
 You will have to retype this query using an explicit cast

We are planning on working on the automatic type conversion
capabilities; I'll add this to the list of things to address. However,
note that this capability is in Postgres; it is misleading to state that
it is not:

  tgl=> select cast('abc' as text) || 'def';
  --------
  abcdef
  (1 row)

Oh, that's right, we don't support CAST() :)

> > function left:
> > the test is select left('abcd',2)
> > looked in the manual of postgres and couldn't find the function
> > left.
> > this isn't working and I tried some other ways but couldn't find any
> > working query.
> Yep, we don't have it.  We have lots of them in the oracle_compat
> module, but not that one.

Wouldn't this old Oracle/Ingres function be superceded by the SQL92
function SUBSTRING()? We do have that, in all its glory. Doesn't appear
in you comparison table though.

> > function current_date:
> > the test is select current_date()
> > that's postgres not supporting only select current_date
> > that's also the case with current_time, current_timestamp. I will
> > make patch so it's tested both ways.
> Does the standard say we need those parens?

SQL92 defines the syntax to be CURRENT_DATE or CURRENT_DATE(integer),
where the integer defines a precision for the seconds field. The parens
without the integer is non-standard afaik. Postgres has all of the forms
in the SQL92 standard.

> > Function IN on strings in SELECT:
> > the test is: select 'monty' in ('david','monty','allan')
> > and that's not working. it gives: using an explicit cast
> Darn type system again.

Now, Bruce, learn to say "that great type system again", with feeling :)

> > case insensitive compare:
> > query: select b from crash_me where b = 'A'
> > query: select b from crash_me where b = 'A' didn't return any
> > result no (field b in table crash_me has a value of 'a'.)
>
> We use ~* for this.  How do you control case-sensitivity?  Are all
> strings compared for lower and upper case.
> > the transaction are still not inserted in the crash-me test because
> > monty and I didn't have time to insert it. But what I mentioned
> > before if you would like to see some other tests in the crash-me
> > test you can add them by your self and send us the patch. We will
> > add it to the release of crash-me.

How about if we send the full Postgres regression test? That should
uncover a few differences :)

> > I am not sure the results of postgres in the crash-me page is from
> > the final release of postgres. Because I have so much problems with
> > postgres it's at this moment hard to test.

??? Better stick with MySQL then...

> > Further it's taking a very
> > lot of time to test the postgres db because it's eating my memory.
> > When I test the limit of the number of joins I can do it's eating my
> > memory till there is no memory left anymore. And that's a lot (96 mb
> > real + 470mb swap).

Yup. Those joins do like to chew up memory at the moment.

It seems that the result of the test is to verify MySQL compatibility,
rather than SQL92 compatibility or some other standard. It's OK if
Postgres doesn't pass every test on non-standard features, but it's
annoying that the test isn't labeled more appropriately. In fact, it
actually claims to be trying to, in an unbiased way, help users select a
database product. Sorry, doesn't quite conform to labeling.

OK, some feedback on specific information in "crash-me":

SQL92 specifies that the allowed range in precision and scale for
numeric() and decimal() types is implementation-defined. By definition
Postgres does support both of these ANSI types.

For "Other Types", Postgres has:

abstime
bool
box
char16
char2
char4
char8
circle
datetime
filename
float4
float8
int2
int28
int4
line
lseg
money
name
path
point
polygon
reltime
text
timespan
tinterval
unknown

Plus the SQL92 ones already listed and some others which are
Postgres-specific and not oriented toward application support; we
wouldn't expect you to need to list those :)

We support the CAST() function; I had the syntax wrong prior to v6.3.
We support the "||" operator, even though your specific test causes
trouble because of the unspecified types in the test.
We support CHAR_LENGTH() and CHARACTER_LENGTH(), but which have the same
problem in your test as "||".

We support CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.
We support CURRENT_USER, which does not show up in your comparison chart
at all. My reference books do not mention SYSDATE; what standard is that
from?

In "Other Functions", ~800 of the 835 Postgres functions are not listed.
As you might know, most capabilities in Postgres are implemented as
functions, so perhaps only ~300 of these unlisted ones are oriented
toward application support. Shall I send you a list? :)

My reference books do not list COUNT in a context other than COUNT(*)
and COUNT(column). What is the Oracle COUNT and why is it an ANSI
function?

In the "Function Use" table, why would PostgreSQL be listed as not
supporting "update of column = -column"?

  tgl=> update t set i = -i;
  UPDATE 2

seems to work for me...

In "Join Methods", Postgres supports subqueries.

In "String Handling", Postgres does have extensive comparision support,
including "case insensitive compare". We do not allow double-quote to
delimit strings, since that character is defined for other uses in
SQL92. Do you show "Has Delimited Identifiers" as a feature? Postgres
has them, as called for in SQL92.

How about binary and hexadecimal integer input? Postgres has all of
these SQL92 features using SQL92 syntax. Is this the same comparison as
the non-standard "binary items (0x41)" in the Type Limits table?

In the "Index Limits" table, what do you mean by "null in index"?
Postgres supports indices containing null values. I guess you mean
something more specific than is indicated in the label.

In the "Type Limits" table, Postgres does support both items "YY-MM-DD
dates" and "0000-00-00 dates", as well as other permutations not
appearing in your table. Postgres supports "European dates" (e.g.
"16-03-1998") as well as "German dates" and "free-form text" dates (e.g.
"Mon Mar 16 17:55:54 1998 MET"). Postgres has full support for time
zones, not just the limited and brain-damaged support called for in
SQL92.

In "Comments", Postgres supports "--" as a comment delimiter.

In "Other Features", Postgres supports "foreign key syntax", although it
does not yet support foreign keys. Postgres supports "LOCK table", but
does not have "LOCK TABLE table". Neither are SQL92 standard anyway.

In "Other Limits", Postgres' maximum number of connections can be
specified at compile-time. There is no theoretical limit to these other
than operating system limits. Those maximums have a different meaning
for Open Source software than for commercial packages, where you cannot
change these kinds of parameters.

"crash-me" is a specific test which does not warrant extending its
results to the tabular summary you are posting. It may be a helpful tool
to elicit many of the entries in that summary, but any other use is
misleading at best.

Thanks for your time...

                     - Tom

On Mon, 16 Mar 1998, Thomas G. Lockhart wrote:

> > > first the concatenation with ||
> > > the test is select 'abc' || 'def'
> > > and that's not working in postgres. In other db's it's working.
> >
> > Looks like we have a type problem here.  That type system again.
>
> "that type system" is A Good Thing(tm).
>
> tgl=> select 'abc' || 'def';
> ERROR:  There is more than one operator '||'
>  for types 'unknown' and 'unknown'
>  You will have to retype this query using an explicit cast

This sentence doesn't work on MySQL too, take a look:

mysql> select 'abc' || 'def';
1 row in set (0.00 sec)
+----------------+
| 'abc' || 'def' |
+----------------+
|              0 |
+----------------+
The result should be 'abcdef' not 0 !!

On 17 Mar 98 at 9:56, sferac@bo.nettuno.it wrote:
> This sentence doesn't work on MySQL too, take a look:
>
> mysql> select 'abc' || 'def';
> 1 row in set (0.00 sec)
> +----------------+
> | 'abc' || 'def' |
> +----------------+
> |              0 |
> +----------------+
> The result should be 'abcdef' not 0 !!

And that's why it's printed on the crash-me page that it gives an
error (wrong output).

Greetz..
Luuk.
|-----------------------------------------------------------------------|
|  Luuk de Boer -> <luuk@pi.net>                                      |
|  When you want to go fast .....                                       |
|  Drive a cobra on the road and take a T3 line for internet ...... :-) |
|  http://www.luuk.com/  -> The AC Cobra site                           |
|-----------------------------------------------------------------------|

On 16 Mar 98 at 17:22, Thomas G. Lockhart wrote:

> > > first the concatenation with ||
> > > the test is select 'abc' || 'def'
> > > and that's not working in postgres. In other db's it's working.
> >
> > Looks like we have a type problem here.  That type system again.
>
> "that type system" is A Good Thing(tm).
>
> tgl=> select 'abc' || 'def';
> ERROR:  There is more than one operator '||'
>  for types 'unknown' and 'unknown'
>  You will have to retype this query using an explicit cast
>
> We are planning on working on the automatic type conversion
> capabilities; I'll add this to the list of things to address. However,
> note that this capability is in Postgres; it is misleading to state that
> it is not:
>
>   tgl=> select cast('abc' as text) || 'def';
>   --------
>   abcdef
>   (1 row)
>
> Oh, that's right, we don't support CAST() :)

crash-me is intended to test how compatible (ANSI SQL, ODBC or some
other standard) a SQL server is. If the server doesn't follow the ANSI
SQL syntax for a specific function it can't pass that test. MySQL also
fails a couple of test because of this (like the above test).

The only exception to the above is the 'extra functions' section where
we try to add all possible useful functions from all possible
databases.

>
> > > function left:
> > > the test is select left('abcd',2)
> > > looked in the manual of postgres and couldn't find the function
> > > left.
> > > this isn't working and I tried some other ways but couldn't find any
> > > working query.
> > Yep, we don't have it.  We have lots of them in the oracle_compat
> > module, but not that one.
>
> Wouldn't this old Oracle/Ingres function be superceded by the SQL92
> function SUBSTRING()? We do have that, in all its glory. Doesn't appear
> in you comparison table though.

left() is an ODBC function and substring() is a SQL92 function. The
left() function is tested in the odbc section and substring is tested
in the ansi sql section.

>
> > > function current_date:
> > > the test is select current_date()
> > > that's postgres not supporting only select current_date
> > > that's also the case with current_time, current_timestamp. I will
> > > make patch so it's tested both ways.
> > Does the standard say we need those parens?
>
> SQL92 defines the syntax to be CURRENT_DATE or CURRENT_DATE(integer),
> where the integer defines a precision for the seconds field. The parens
> without the integer is non-standard afaik. Postgres has all of the forms
> in the SQL92 standard.

is fixed in the next release of crash-me.

> > > case insensitive compare:
> > > query: select b from crash_me where b = 'A'
> > > query: select b from crash_me where b = 'A' didn't return any
> > > result no (field b in table crash_me has a value of 'a'.)
> >
> > We use ~* for this.  How do you control case-sensitivity?  Are all
> > strings compared for lower and upper case.

MySQL has two classes of strings.  Depending on context all
strings are compared binary are case sensitively.  ALL compare
functions works with binary and case sensitive strings.  The ~* is
more like a shortcut for LOWER(string) = LOWER(string).

> > > the transaction are still not inserted in the crash-me test because
> > > monty and I didn't have time to insert it. But what I mentioned
> > > before if you would like to see some other tests in the crash-me
> > > test you can add them by your self and send us the patch. We will
> > > add it to the release of crash-me.
>
> How about if we send the full Postgres regression test? That should
> uncover a few differences :)

As the PostgreSQL regression test doesn't test any limits of
PostgreSQL they aren't really that important.  It would be nice if
some of the PostgreSQL developers really put some time into looking at
crash-me and add some interesting tests to get a better and fairer
comparison, not only between MySQL and PostgreSQL but also against
other databases.

>
> > > I am not sure the results of postgres in the crash-me page is from
> > > the final release of postgres. Because I have so much problems with
> > > postgres it's at this moment hard to test.
>
> ??? Better stick with MySQL then...

the problem is is that postgres isn't very stable and the releases
are not well tested. Approx 6 months ago some postgres developers
mentioned something about including crash-me with postgres and
testing postgres with crash-me before releasing it, but I think that
didn't happen ...

>
> It seems that the result of the test is to verify MySQL compatibility,
> rather than SQL92 compatibility or some other standard. It's OK if
> Postgres doesn't pass every test on non-standard features, but it's
> annoying that the test isn't labeled more appropriately. In fact, it
> actually claims to be trying to, in an unbiased way, help users select a
> database product. Sorry, doesn't quite conform to labeling.

I had the same discussion a while ago in the questions mailinglist.
As the crash-me page states, there is still a lot todo with crash-me.
And I think a lot of developers will agree with me that documention
comes always at last while developing something. We looked around on
the net and didn't find any other test which gives us a good base for
a selection. It shows what doesn't work with different database and
also how compatible a database is to ' standard sql'.

>
> OK, some feedback on specific information in "crash-me":
>
> SQL92 specifies that the allowed range in precision and scale for
> numeric() and decimal() types is implementation-defined. By definition
> Postgres does support both of these ANSI types.

According to ODBC, the precision of numeric() and decimal() must
be at least 15, which is making postgres to fail the above test.

>
> Plus the SQL92 ones already listed and some others which are
> Postgres-specific and not oriented toward application support; we
> wouldn't expect you to need to list those :)

crash-me already list quite many of the above.

>
> We support the CAST() function; I had the syntax wrong prior to v6.3.
> We support the "||" operator, even though your specific test causes
> trouble because of the unspecified types in the test.
> We support CHAR_LENGTH() and CHARACTER_LENGTH(), but which have the same
> problem in your test as "||".

when we test that sort of functions we test it the ANSI SQL way and
not all the ways the different databases are doing it else we could
write thousands of queries for all different db's.

>
> We support CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.
that's fixed as I mailed before.
> We support CURRENT_USER, which does not show up in your comparison chart
> at all. My reference books do not mention SYSDATE; what standard is that
> from?

we looked in our references and couldn't find it that fast we shall
take a look at it where we found it.

>
> In "Other Functions", ~800 of the 835 Postgres functions are not listed.
> As you might know, most capabilities in Postgres are implemented as
> functions, so perhaps only ~300 of these unlisted ones are oriented
> toward application support. Shall I send you a list? :)
>
> My reference books do not list COUNT in a context other than COUNT(*)
> and COUNT(column). What is the Oracle COUNT and why is it an ANSI
> function?

It's an old label which was changed while developing. It will be
changed to the good label.

>
> In the "Function Use" table, why would PostgreSQL be listed as not
> supporting "update of column = -column"?
>
>   tgl=> update t set i = -i;
>   UPDATE 2
>
> seems to work for me...

it didn't in the latest test with me ... I will take a look at it
what is happening ...

>
> In "Join Methods", Postgres supports subqueries.

the crash-me test output which is now in html on crash-me.html is
from a snapshot of postgres (beta version). I have almost run all
tests on all db's again and soon you will see the new crash-me.html
page with the output of the official release of postgres 6.3. There
is subqueries ok ...

>
> In "String Handling", Postgres does have extensive comparision support,
> including "case insensitive compare". We do not allow double-quote to
> delimit strings, since that character is defined for other uses in
> SQL92. Do you show "Has Delimited Identifiers" as a feature? Postgres
> has them, as called for in SQL92.

No, this isn't tested in crash-me yet, Maybe you could send us some
tests?

>
> How about binary and hexadecimal integer input? Postgres has all of
> these SQL92 features using SQL92 syntax. Is this the same comparison as
> the non-standard "binary items (0x41)" in the Type Limits table?

the 0x41 is legal according to the ODBC standard.

>
> In the "Index Limits" table, what do you mean by "null in index"?
> Postgres supports indices containing null values. I guess you mean
> something more specific than is indicated in the label.

This is indead a test if one can have a NULL value in an index
column.
We test this by creating a index, adding a null value and selecting
this. Postgres isn't returning the NULL value.

>
> In the "Type Limits" table, Postgres does support both items "YY-MM-DD
> dates" and "0000-00-00 dates", as well as other permutations not
> appearing in your table. Postgres supports "European dates" (e.g.
> "16-03-1998") as well as "German dates" and "free-form text" dates (e.g.
> "Mon Mar 16 17:55:54 1998 MET"). Postgres has full support for time
> zones, not just the limited and brain-damaged support called for in
> SQL92.

the tests weren't run on postgres yet. But in the run of mine it is
and the test for YY-MM-DD isn't giving the good value back and
the 000-00-00 test is failing.

>
> In "Comments", Postgres supports "--" as a comment delimiter.

Yep I know. I tested it on the prompt but I had todo an return
before I could do \g. I don't know why but the same query is failing
in the test while it is just parsed as all other queries to the
postgres db.

>
> In "Other Features", Postgres supports "foreign key syntax", although it
> does not yet support foreign keys. Postgres supports "LOCK table", but
> does not have "LOCK TABLE table". Neither are SQL92 standard anyway.

maybe not the full foreign key syntax is supported.
Empress,oracle,mysql is supporting lock table and that's why we added
this test to crash-me.

>
> In "Other Limits", Postgres' maximum number of connections can be
> specified at compile-time. There is no theoretical limit to these other
> than operating system limits. Those maximums have a different meaning
> for Open Source software than for commercial packages, where you cannot
> change these kinds of parameters.

You can change most important limits on all commercial SQL servers.
crash-me only tests a default installation if not noted elsewhere. The
crash-me page also states very clearly that some limits may be
changeable with startup options.

Forget the 'Open source software' argument. This isn't true as long as
the default user can't change the code.  A lot of things are also
bound by algorithms that can't be changed very easily.

>
> "crash-me" is a specific test which does not warrant extending its
> results to the tabular summary you are posting. It may be a helpful tool
> to elicit many of the entries in that summary, but any other use is
> misleading at best.
>

crash-me is a open test suite and we encourage anyone to
add new tests to it.  crash-me is also the only SQL server test that
truly shows the most common limits of some of the big database
servers. (We hope to add Sybase, Informix and Adabas shortly).
If you are trying to decide how to code an compatible application,
crash-me is the only source of information you can depend on!

crash-me has also found numerous bugs in the postgreSQL code (and some
other SQL servers) and this also variants its existence :)

Greetz....
Luuk

|-----------------------------------------------------------------------|
|  Luuk de Boer -> <luuk@pi.net>                                      |
|  When you want to go fast .....                                       |
|  Drive a cobra on the road and take a T3 line for internet ...... :-) |
|  http://www.luuk.com/  -> The AC Cobra site                           |
|-----------------------------------------------------------------------|

What does it mean when MySQL says "Ignored" in the CREATE INDEX entry?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> > What does it mean when MySQL says "Ignored" in the CREATE INDEX
> > entry?
>
> mysql isn't supporting create index like the ansi sql way. You must
> create an index in the create of the table with mysql. It's accepting
> create index statements but it isn't doing anything with it so that's
> why it's ignored.

You are holding MySQL and Postgres to different standards when
developing your comparison table. Frankly, it seems that we should meet
somewhere in the middle on this if you are hoping for further
collaboration on developing a test suite :(

                    - Tom

On 17 Mar 98 at 20:13, Bruce Momjian wrote:

> What does it mean when MySQL says "Ignored" in the CREATE INDEX entry?
>

mysql isn't supporting create index like the ansi sql way. You must
create an index in the create of the table with mysql. It's accepting
create index statements but it isn't doing anything with it so that's
why it's ignored.

Greetz...

Luuk
|-----------------------------------------------------------------------|
|  Luuk de Boer -> <luuk@pi.net>                                      |
|  When you want to go fast .....                                       |
|  Drive a cobra on the road and take a T3 line for internet ...... :-) |
|  http://www.luuk.com/  -> The AC Cobra site                           |
|-----------------------------------------------------------------------|

On Wed, 18 Mar 1998, Luuk de Boer wrote:

> > > > I am not sure the results of postgres in the crash-me page is from
> > > > the final release of postgres. Because I have so much problems with
> > > > postgres it's at this moment hard to test.
> >
> > ??? Better stick with MySQL then...
>
> the problem is is that postgres isn't very stable and the releases
> are not well tested.

    What *are* you talking about?  This is based on extensive
experience with which version??  Which platform "isn't very stable", as
I'm running on Solaris/sparc and FreeBSD here, and haven't seen any
instability problems since v6.0 ... Vadim is running it on a FreeBSD box
with a several GB(?) database ... hell, someone I know from my past sent
me email the other day saying he was switching from msql to postgresql
because he was seeing 10x faster performance doing the same thing on Karl
Denninger's server...

> Approx 6 months ago some postgres developers
> mentioned something about including crash-me with postgres and
> testing postgres with crash-me before releasing it, but I think that
> didn't happen ...

    Actually, it couldn't have been more then 4 months ago, since the
"some postgres developers" was me, and i know it was *since* v6.2.1 was
released, as I was hoping to get it into v6.3...

Luuk de Boer wrote:
>
> On 17 Mar 98 at 20:13, Bruce Momjian wrote:
>
> > What does it mean when MySQL says "Ignored" in the CREATE INDEX entry?
> >
>
> mysql isn't supporting create index like the ansi sql way. You must
> create an index in the create of the table with mysql.

Not necesarrily. You may use ALTER TABLE to ADD, REMOVE, CHANGE
KEY(s)/INDEX(es).

Mike

--
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND

On 18 Mar 98 at 15:18, Thomas G. Lockhart wrote:

> > > What does it mean when MySQL says "Ignored" in the CREATE INDEX
> > > entry?
> >
> > mysql isn't supporting create index like the ansi sql way. You must
> > create an index in the create of the table with mysql. It's accepting
> > create index statements but it isn't doing anything with it so that's
> > why it's ignored.
>
> You are holding MySQL and Postgres to different standards when
> developing your comparison table. Frankly, it seems that we should meet
> somewhere in the middle on this if you are hoping for further
> collaboration on developing a test suite :(
>

Yep that's true but because it's open for everyone you can't expect
from us that we will write all those changes. Hopefully someone of
the postgres developers will say .... let's take a look at it and
let's add some things for postgres in crash-me. And maybe it's
interesting for the postgres people to add an sub select benchmark in
the benchmark. We aren't going to write it till mysql is supporting
it.

Greetz...

Luuk
|-----------------------------------------------------------------------|
|  Luuk de Boer -> <luuk@pi.net>                                      |
|  When you want to go fast .....                                       |
|  Drive a cobra on the road and take a T3 line for internet ...... :-) |
|  http://www.luuk.com/  -> The AC Cobra site                           |
|-----------------------------------------------------------------------|