If the question is about the performance, it is difficult to answer, because each database can be configured to have better performance in a special case. According to the results of our internal tests, the best choice is Oracle 11G R2 even when you store the binary data in the database. For other databases, it is recommended to store the binary data in the file system unless you have only small file content to store. MySQL and PostgreSQL are also demonstrated in our benchmark results that they could provide good performance. DB2 and MSSQL are slower in default configurations. The default configuration of Sybase is the slowest, but in this question, take the database server maintenance into account. The installation of MySQL and PostgreSQL is simple and they can work even on any limited hardware. The same actual for maintenance during the work. Note for Sybase: "check-sns-new-connection" data container configuration parameter should be set to "true". For testing purpose, embedded database such as HSQLDB is the best choice. Apache Derby and H2 are also supported. But, H2 surprisingly needs "beta" feature enabled - MVCC=TRUE in JDBC URL.

MySQL database should be created in utf8 encoding with collation utf8_bin, since eXo JCR is case-sensitive:

CREATE DATABASE db1 CHARACTER SET utf8 COLLATE utf8_bin;

The eXo JCR application (for example, GateIn) should use JCR dialect "MySQL-UTF8".

Index's key length of JCR_SITEM (JCR_MITEM) table for mysql-utf8 dialect is reduced to 765 bytes (or 255 chars).

To enable JCR to work properly with Sybase, a check-sns-new-connection property with the 'false' value is required for each workspace data container:

<container class="org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer">

    <properties>

        <property name="source-name" value="jdbcjcr" />

        <property name="dialect" value="auto" />

        <property name="multi-db" value="true" />

        <property name="update-storage" value="false" />

        <property name="max-buffer-size" value="200k" />

        <property name="swap-directory" value="target/temp/swap/ws" />

        <property name="swap-directory" value="target/temp/swap/ws" />

        <property name="check-sns-new-connection" value="false" />

    </properties>

No, direct access to items via JCR API is more efficient. Search will consume additional resources for index querying and only then return the items.

No, it is not supported. There are two ways to order results, when the path may be used as criteria:

Order by jcr:path

If no order specification is supplied in the query statement, implementations may support document order on the result nodes (see 6.6.4.2 Document Order of JSR-170), and it is sorted by order number.

By default, (if query does not contain any ordering statements) result nodes are sorted by the document order.

SELECT * FROM nt:unstructured WHERE jcr:path LIKE 'testRoot/%'

For specified jcr:path ordering, there is different proceeding in XPath and SQL: