[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          |     Focuses:
-------------------------------+-----------------------------

Comment (by charlestonsw):

 An article I wrote tonight: [[http://www.storelocatorplus.com/wordpress-
 dbdelta-better-in-4-2-not-yet-perfect/ DBDelta Review in 4.2.2]].   This
 is after cleaning up several websites that had n+ indices on the same
 exact key phrase.   The clean-up happened after tracing performance
 issues.   Turns out having a few-dozen of the same index declaration on a
 table can make for some very slow add/update operations on those tables.

 I saw some [[https://wordpress.slack.com/archives/core/p1414565793002046
 threads on Slack last October]] about forcing dbDelta() to adhere to a
 "subset of clean MySQL".    No problems here, but we have a notable issue,
 IMO, when forcing plugin and theme developers to use this subset of SQL
 degrades the performance of websites that employ otherwise-well-written
 plugins.   Especially when the new docs that tell devs "the right way"
 were just updated 6 months ago.  There are 22,000+ plugins that wrote
 their CREATE TABLE command years ago and I doubt they will ever read that
 doc page again.


 If you scan the plugin (and theme) source files you will find numerous
 cases where these two things happen quite frequently:

 1) PRIMARY KEY is NOT followed by double spaces.

 2) KEY IS followed by more than ONE space.

 Item #1 does not cause long term performance issues.    However it does
 force a data structure I/O on every single theme/plugin update.   I just
 fixed this double-space issue in my plugin.  For clients that are hosting
 250,000+ locations in my custom table that one "small thing" that was
 hidden from the user was causing a  VERY LONG WAIT during a simple plugin
 update while MySQL recreate, unnecessarily, a primary key.

 Item #2 is a bigger issue.   This creates duplicate indices on a table.
 If a table has 5 indices defined and the user updates 10 times over a
 couple of years they have 50+ indices.   It is only a matter of time
 before MySQL starts throwing out more problematic maximum index count
 errors and warnings.   Upgrades take longer.  Data I/O takes longer.
 All-in-all not good for the performance of the WordPress site.

 The question is:

 '''Do you force developers to adhere to very strict standards when using
 dbDelta()? '''

 If so,
 [[http://codex.wordpress.org/Creating_Tables_with_Plugins#Creating_or_Updating_the_Table
 the online doc]] updates are a step in the right direction but they need
 to be a LOT MORE DETAILED.

 Also, you need to consider that many users of WordPress plugins and themes
 will suffer the hidden consequences of poor performance as the "index
 cruft" builds up through subsequent updates.

 Yes, core is clean, but WordPress on a install site is an all-inclusive
 system.

 IMO, dbDelta() should do whatever it can, within reason, to address basic
 variances in SQL statement structures.  At the very least addressing
 idiosyncrasies in the KEY command itself "double space here, single space
 there" should be dealt with to make it consistent.  That would be far more
 disruptive to core, however.

 I'm willing to write some "clean up inbound SQL for dbDelta" code and
 build on the recent phpUnit tests that was noted on another ticket to
 validate the changes, but I don't want to burn time on Core code that
 won't get used.

 My plugin now adheres to all of the quirks of dbDelta, so this is not a
 "for my plugin" concern.   It is a concern for all those sites that now
 have 64 indexes in place, are taking a performance hit every single day
 without anyone having a clue.

 The next question is how flexible do you make this?    What is the real
 performance hit?  dbDelta is run how often v. index queries and updates?
 If there is a concern, wrap a "DBDELTA_STRICT_DEFINES" constant around
 code you don't want to add the overhead to.    Combine that with the
 newly-introduced globals list and skip the extra "massaging of SQL" for
 those things as well.       The performance hit could be minimal while
 making dbDelta more code friendly.


 As an aside, there are a LOT of plugins that use whitespace formatting to
 make nice readable code, using extra spaces to line up the column names
 and index names, then the field declaration.  That is what my code used to
 look like, an was far easier to read the data structure from.    Clean
 readable code and the dbDelta functional requirements are in direct
 opposition.


 Possibly big changes going into dbDelta()... scary stuff given the
 potential impact.   Maybe starting with a grep of plugin & theme code on
 the repo and getting some idea of malformed SQL is warranted to determine
 just how far-reaching this issue is.

 Core Team comments?

--
Ticket URL: <https://core.trac.wordpress.org/ticket/10404#comment:16>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform