You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by "Rollo, Dan" <dr...@ets.org> on 2003/01/10 20:20:33 UTC
RE: WANTED: People familar with SQL particulars for non-MySQL ser vers
A thought: Is it possible to just reverse the order of the fields in the
Primary Key?
As in change:
PRIMARY KEY (message_name, repository_name),
to:
PRIMARY KEY (repository_name, message_name),
In many databases, I think this allows the index (PK) to be used to optimize
queries that filter by the repository_name, as well as those that use both
repository_name and message_name. (With the old order, queries filtering by
repository_name would not use the index).
Dan
-----Original Message-----
From: Noel J. Bergman [mailto:noel@devtech.com]
Sent: Friday, January 10, 2003 2:08 PM
To: James-User Mailing List
Subject: WANTED: People familar with SQL particulars for non-MySQL
servers
James currently has support for several SQL database servers, using JDBC.
There are specific sections for MySQL, MSSQL, Oracle, PostgreSQL, SAP, and
HyperSonic. Each of these database servers has certain ... SQL subtleties.
Recently we made a change for the benefit of MySQL, which I would like to
reproduce for the other servers.
The change effects the definitions for JDBCMailRepository operations.
Specifically, the createTable operation for MySQL has a secondary index key.
This looks like:
<sql name="createTable" db="mysql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body longblob NOT NULL ,
last_updated datetime NOT NULL,
PRIMARY KEY (message_name, repository_name),
added --> KEY repo (repository_name)
)
</sql>
There are similar statements for the other database servers, but without the
additional index. Rather than make a change myself for a server that I
don't have available to test, I am asking if those of you who are familar
with each of the others would please submit the appropriate change to me so
that I can incorporate it into a future release of James.
Thanks. :-)
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
**************************************************************************
This e-mail and any files transmitted with it may contain privileged or
confidential information. It is solely for use by the individual for whom
it is intended, even if addressed incorrectly. If you received this e-mail
in error, please notify the sender; do not disclose, copy, distribute, or
take any action in reliance on the contents of this information; and delete
it from your system. Any other use of this e-mail is prohibited. Thank you
for your compliance.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: WANTED: People familar with SQL particulars for non-MySQL ser
vers
Posted by Serge Knystautas <se...@lokitech.com>.
Noel J. Bergman wrote:
>>The goal of this is to speed-up queries with a WHERE statement that
>>specifies only the repository_name, correct?
>
>
> Yes.
>
> Are you agreeing with Dan Rollo that most servers should work the same way
> that MySQL works, where we can just optimize the order of a multiple column
> index instead of creating another index? Are you confirming that MSSQL
> works that way?
>
> I've made that change here. If the consensus is that it is effective on
> enough systems, I'll commit it and mark off that item from the project list.
MSSQL def. works that way, we're pretty sure Oracle does as well, and I
would guess any other commercial server would. Can't speak for
hypersonic or postgresql. If all we're changing is the ordering of an
index/key, I would say make the change as I can't see it breaking
anything. Then as needed, the various db users can test and submit a
patch to create the second index if their DB needs it.
--
Serge Knystautas
Loki Technologies
http://www.lokitech.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: WANTED: People familar with SQL particulars for non-MySQL ser vers
Posted by "Noel J. Bergman" <no...@devtech.com>.
Serge,
> The goal of this is to speed-up queries with a WHERE statement that
> specifies only the repository_name, correct?
Yes.
Are you agreeing with Dan Rollo that most servers should work the same way
that MySQL works, where we can just optimize the order of a multiple column
index instead of creating another index? Are you confirming that MSSQL
works that way?
I've made that change here. If the consensus is that it is effective on
enough systems, I'll commit it and mark off that item from the project list.
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: WANTED: People familar with SQL particulars for non-MySQL ser
vers
Posted by Serge Knystautas <se...@lokitech.com>.
Rollo, Dan wrote:
> A thought: Is it possible to just reverse the order of the fields in the
> Primary Key?
> As in change:
> PRIMARY KEY (message_name, repository_name),
> to:
> PRIMARY KEY (repository_name, message_name),
>
> In many databases, I think this allows the index (PK) to be used to optimize
> queries that filter by the repository_name, as well as those that use both
> repository_name and message_name. (With the old order, queries filtering by
> repository_name would not use the index).
Good point... checking over all the SQL statements, everytime we query
with message_name, we also query with repository_name. So with the
index/primary key order reversed, the queries that just use
repository_name can benefit from the single index.
--
Serge Knystautas
Loki Technologies
http://www.lokitech.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: WANTED: People familar with SQL particulars for non-MySQL servers
Posted by "Noel J. Bergman" <no...@devtech.com>.
Dan,
I believe that your belief is generally correct. In the case of MySQL,
section 5.4.5 Multiple-Column Indexes says "MySQL uses multiple-column
indexes in such a way that queries are fast when you specify a known
quantity for the first column of the index in a WHERE clause, even if you
don't specify values for the other columns." I probably will make the
ordering change, and back out Danny's additional KEY statement, unless he
objects.
But I don't know if all of the other servers work that way, too, or not.
That is why I posted my note. I'm hoping to hear back from people using
each of the databases. I don't have them here to test against, nor do I
feel like looking up the documentation for each server's dialects and
nuances. All I'm trying to do is get people using those other servers to
review the SQL, and let us know if there are optimizations we can do for
their benefit.
--- Noel
-----Original Message-----
From: Rollo, Dan [mailto:drollo@ets.org]
Sent: Friday, January 10, 2003 14:21
To: 'James Users List'
Subject: RE: WANTED: People familar with SQL particulars for non-MySQL
ser vers
A thought: Is it possible to just reverse the order of the fields in the
Primary Key?
As in change:
PRIMARY KEY (message_name, repository_name),
to:
PRIMARY KEY (repository_name, message_name),
In many databases, I think this allows the index (PK) to be used to optimize
queries that filter by the repository_name, as well as those that use both
repository_name and message_name. (With the old order, queries filtering by
repository_name would not use the index).
Dan
-----Original Message-----
From: Noel J. Bergman [mailto:noel@devtech.com]
Sent: Friday, January 10, 2003 2:08 PM
To: James-User Mailing List
Subject: WANTED: People familar with SQL particulars for non-MySQL
servers
James currently has support for several SQL database servers, using JDBC.
There are specific sections for MySQL, MSSQL, Oracle, PostgreSQL, SAP, and
HyperSonic. Each of these database servers has certain ... SQL subtleties.
Recently we made a change for the benefit of MySQL, which I would like to
reproduce for the other servers.
The change effects the definitions for JDBCMailRepository operations.
Specifically, the createTable operation for MySQL has a secondary index key.
This looks like:
<sql name="createTable" db="mysql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body longblob NOT NULL ,
last_updated datetime NOT NULL,
PRIMARY KEY (message_name, repository_name),
added --> KEY repo (repository_name)
)
</sql>
There are similar statements for the other database servers, but without the
additional index. Rather than make a change myself for a server that I
don't have available to test, I am asking if those of you who are familar
with each of the others would please submit the appropriate change to me so
that I can incorporate it into a future release of James.
Thanks. :-)
--- Noel
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>