MySQL

Buffer Pool Size
For large databases you may need to adjust some settings for the MySQL server. Most important of these is probably the Buffer Pool Size parameter for InnoDB tables. This affects the amount of information that is held in memory by MySQL. Ideally all the indexes that are in common use should fit inside this buffer. For large databases (many millions of rows) you may benefit from increasing this parameter, and for a dedicated database server you may want a significant amount of you available RAM set aside to this.
Use cursors
By default MySQL retrieves the complete result set before sending it to the client. It does this to optimise performance by avoiding the need to go back to the database for more data. Most other databases use a cursor, where only a small number of rows are actually retrieved, and to get all the rows means going back to the database for more, which can result in many round trips to the database. Which approach is faster depends on the exact usage and there are no hard and fast rules.

IJC generally breaks data retrieval into small chunks anyway, so the default MySQL approach is usually fine. However there is one exception, and that is retrieval of the hit list when retrieving initial data or running queries. For very large tables (millions of rows) the hit list can be large, and MySQL can take a long time to build it all. In these cases using a cursor can improve performance significantly. MySQL can be told to use a cursor by changing the JDBC parameters. This is done by changing the JDBC URL that is defined as part of the connection parameters (r-click on the IJC schema node in the projects window and choose 'Schema settings...'). Change the JDBC URL parameter and append the appropriate parameter to it. An example is:

The

parameter tells MySQL to use a server based cursor, and the

parameter tells it to fetch 1000 rows at a time. You may want to experiment with this value to see if higher or lower values work best for you.

See here for more details about the MySQL JDBC driver properties.

If you have any other database tuning tips that you think would be useful then please let us know and we will add them here.

1. Situations where adding an index is strongly recommended

In these situations adding an index will always be expected to be of big benefit:

• When setting the 'Distinct values' setting for a field. This will dramatically improve the retrieval of these distinct values e.g. for display in the query panel.

• When merging data using a field that does not already have an index. Merging needs the value to be looked up for each imported entry, and an index will dramatically speed this up.

2. Situations where adding an index may be beneficial

In these situations adding an index may be of benefit (but be careful to consider the potential downside listed in the next section):

• When regularly using a field in a large table for querying or sorting adding an index to the columns used by the field will usually improve performance.

3. Situations where adding an index should be avoided

In these situations adding an index may have a detrimental effect:

• When the data in the table is frequently being inserted, updated or deleted. When this happens the index also has to be updated and this takes additional time.

• For data where the number of distinct values present is not much less that the total number of rows. e.g. for continuously variable numeric data such as molecular weight.

  Covering indexes

The nature of typical searches in IJC warrants a special mention of what are sometimes called 'covering indexes'. Most searches on non-structure fields in IJC end up as looking like this at the database level:

(we assume a JChem table here for simplicity, and assume that we are querying on the molweight field, but the same applies to many searches). In this type of search there are a very large number of different values for the molweight, almost as many as there are rows. Adding an index to this type of field can make many searches slower not faster, especially for queries that return lots of rows (like the one above). The reason is that the index will be consulted for the values that match, but the table has then to be consulted for the cd_id value, and this results in an excessive amount of IO operations, and leads to worse performance than if the index was not present at all.

A solution that can remedy this situation is to add the cd_id column to the index as a second column. Indexes can be composed of multiple columns, and in this case you would create an index with the cd_molweight column as the first column and the cd_id column as the second column. This is often referred to as a 'covering index'. With this type of index the cd_id values can be obtained directly from the index, and the main table does not need to be consulted, resulted in significantly less IO and much better performance.

However it must be stressed that whilst this solution sometimes works well it should be tested in every case to check that it is a good solution for the typical queries that will be run.

Do not use the 'covering index' approach if the column you are indexing has a small number of distinct values (e.g. a supplier name or id). Only use it when most values in the column are different. For columns having a small number of distinct values try using a normal index with just the single column.