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:[EMAIL PROTECTED]]
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:[EMAIL PROTECTED]]
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: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
