Editing Common MySQL Problems with EPrints (section)

=== Resolution ===
There is a two-fold approach to this.  One part if to change the EPrints field configuration and set the <code>sql_index</code> attribute to 0 for types of field that have this set to 1 by default.  However, this will only be useful on the fields that are complained about when running <code>epadmin update</code>, as their indexes will not have been created due to the limit being reached and therefore you would just be removing the need for them being created.  However, some of these new fields may benefit from having indexes but some existing fields may not or at least benefit significantly less.  The best way to determine this is to look at the existing indexes for the MySQL table for the particular data object, typically <code>eprint</code>.  The following MySQL command can retrieve this information:
 SHOW INDEX FROM eprint;
Then there are a number of considerations about which fields could do without an index.  Generally, those that store numerical values where comparisons are required greatly benefit from an index, so date/time fields like <code>datestamp</code>, <code>lastmod</code> and <code>date</code> greatly benefit from an index, especially as it combines the six separate MySQL columns that make up these EPrint fields (i.e. year, month, day, hour minute and second).  The fields that are less important to have index are the ones that are sparsely used, so fields specific to a particular type of eprint that is not commonly used.  A rough guide is that if the field is used in your archive's advanced search or staff search for eprints, then you would not want to remove its index.  

The following standard <code>eprint</code> fields fit into the category of non-numeric and/or sparsely used, roughly in ascending order or use: <code>gscholar_cluster</code>, <code>gscholar_impact</code>, <code>template</code>, <code>pedagogic_type</code>, <code>num_pieces</code>, <code>succeeds</code>, <code>importid</code>, <code>monograph_type</code>, <code>sword_depositor</code>, <code>thesis_name</code>, <code>thesis_type</code>, <code>event_type</code>, <code>pres_type</code> and <code>contact_email</code>.  However, the reason you will have hit your limit of keys is due to non-standard fields that will have been added to the archive so it is worth reviewing which of these are used sparsely and do not contain numeric values that are likely to have comparators or other mathematical operations applied to them.

Once you have identified the fields that least need indexes as well as setting the <code>sql_index</code> attribute for the field to 0 (and reloading the webserver and EPrints indexer), you will need to make sure the index has been dropped from MySQL itself. Using a MySQL command like the following:
 DROP INDEX gscholar_cluster_0 ON eprint;
You will note that the index will have a slightly different name to the field.  You should the <code>SHOW INDEX</code> MySQL command above to determine the correct name for the index.  After doing this you should be able to add new fields without seeing the "Too many keys" error message.  However, be sure to set <code>sql_index</code> to 0 on new fields you add if they are of a type that creates an index but you expect them to be sparsely used.