[wp-trac] [WordPress Trac] #10404: dbDelta creates duplicate indexes when index definition contains spaces

[WordPress Trac]

#10404: dbDelta creates duplicate indexes when index definition contains spaces
-------------------------------+-----------------------------
 Reporter:  Denis-de-Bernardy  |       Owner:
     Type:  enhancement        |      Status:  new
 Priority:  normal             |   Milestone:  Future Release
Component:  Database           |     Version:  2.8.1
 Severity:  normal             |  Resolution:
 Keywords:  has-patch          |
-------------------------------+-----------------------------

Comment (by charlestonsw):

 A new patch file "10404.patch_caseinsensitive_and_noindexname_compare​"
 has been uploaded to help reduce the number of duplicate indices that are
 created by plugins that do not use the WordPress index creation
 methodology.   Someone should probably make some notes around dbDelta in
 the WordPress docs as well as the syntax is rather specific.    What the
 patch has:

 - A good bit more phpDoc info.

 - A LOT more comments around each code block.

 - Case insensitive comparison of index declaration blocks by shifting both
 the incoming index creation string and the metadata build strings to
 uppercase.

 - If the original version of the index comparison fails to find an EXACT
 match, it then falls back to check for "simple index keys".  These are
 keys WITHOUT an index name defined, such as KEY (id) to index the id
 field.

 The last part, which is well commented in the code, looks at the metadata-
 derived strings and converts the string that has been built as KEY
 <idxname> (<fldname>) and drops the optional index name portion if the
 index name EXACTLY MATCHES the field name portion.

 In other words it converts the metadata-derived KEY <idxname> (<fldname>)
 to KEY (<fldname>) if both idxname and fldname are identical.

 Since many plugins seem to like to use the shorthand KEY (<fldname>)
 syntax for building keys, this may help reduce the "too many keys" errors
 and improve performance during record insert/update operations on the
 plugin-provided tables.

 This does not fix ALL possible dbDelta "too many keys" errors but it
 should help reduce the most common errors.

 For developers the best method for using dbDelta with regard to key
 building is:

 - Always use an uppercase KEY directive.

 - Always include an index name in lowercase.

 - Always include your field list in lowercase.

 - Do not add field lengths to the keys.

 - When creating compound keys always eliminate whitespace.  The means no
 spaces before or after parens or around commas.

 In other words:
 KEY mycompoundkey (id,slid)

 Not:
 key ( id , slid )


 Personally I find the later (spaces around parens) hard to do after
 training myself to start ADDING spaces around parens to meet the WordPress
 coding standards which dictate logic operations to be ( a !== b ) versus
 (a!==b). :/

 HTH some other code geeks out there.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/10404#comment:13>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software