A {@link DataModel} backed by a MySQL database and accessed via JDBC. It may work with other JDBCdatabases. By default, this class assumes that there is a {@link DataSource} available under the JNDI name"jdbc/taste", which gives access to a database with a "taste_preferences" table with the following schema:
| user_id | item_id | preference |
|---|---|---|
| 987 | 123 | 0.9 |
| 987 | 456 | 0.1 |
| 654 | 123 | 0.2 |
| 654 | 789 | 0.3 |
preference must have a type compatible with the Java float type. user_id and item_id should be compatible with long type (BIGINT). For example, the following command sets up a suitable table in MySQL, complete with primary key and indexes:
CREATE TABLE taste_preferences ( user_id BIGINT NOT NULL, item_id BIGINT NOT NULL, preference FLOAT NOT NULL, PRIMARY KEY (user_id, item_id), INDEX (user_id), INDEX (item_id) )
See the notes in {@link AbstractJDBCDataModel} regarding using connection pooling. It's pretty vital toperformance.
Some experimentation suggests that MySQL's InnoDB engine is faster than MyISAM for these kinds of applications. While MyISAM is the default and, I believe, generally considered the lighter-weight and faster of the two engines, my guess is the row-level locking of InnoDB helps here. Your mileage may vary.
Here are some key settings that can be tuned for MySQL, and suggested size for a data set of around 1 million elements:
Also consider setting some parameters on the MySQL Connector/J driver:
cachePreparedStatements = true cachePrepStmts = true cacheResultSetMetadata = true alwaysSendSetIsolation = false elideSetAutoCommits = true
Thanks to Amila Jayasooriya for contributing MySQL notes above as part of Google Summer of Code 2007.
| |