News & Info

Daily Updates and Tech Chatter

WordPress dbDelta Bug Affects SLP

There is a bug that remains open in the WordPress Core tracking system that impacts Store Locator Plus database tables.  The bug will degrade database performance after each consecutive update to the Store Locator Plus base plugin.    The problem and resolution are described below.

The Problem

There is a glitch in a core WordPress function, dbDelta(), that creates duplicate table indexes.    Store Locator Plus uses 3 indexes on the locations table.  Indexes significantly increase the performance of database searches when used properly.  For Store Locator Plus an index exists on the store name, the latitude, and the longitude.   The latter two mean that whenever someone does a location search on your website the data returned from the locations table is found much more quickly than if the data index was not in place.  On large installs, those with hundreds or thousands of locations, the difference is notable.

To put the index and any other data table elements in place, SLP uses the built-in dbDelta function from WordPress.  This is the recommended method for managing table and index creation as well as structure updates.  It works great.  Most of the time.

The problem is that dbDelta() has a glitch that creates a new index every time the Store Locator Plus version changes.    It should be looking for the store, lat, and long indexes and if not there it will create them.  Instead if it finds an existing index it creates a new index and adds a numeric suffix.    After a number of upgrades to SLP you end up with a database that has multiple indexes like this:

  • sl_store
  • sl_store_1
  • sl_store_2
  • sl_latitude
  • sl_latitude_1
  • sl_latitude_2
  • sl_longitude
  • sl_longitude_1
  • sl_longitude_2

Those _# indexes are IDENTICAL to the non-suffixed version.

This is bad.   Any time a record is updated or added in the locations table it is MUCH slower as every index needs to be updated.  After just 3 updates MySQL is performing 9 index updates on every record operation versus 3.

It can also confuse and SLOW DOWN record searches as the MySQL engine will take longer to determine which is the best index to use as it has to load the entire index directory.  I assume MySQL is smart enough to drop after the “first perfect match”, but that is purely conjecture.   If that is true the search impact would be minimal.  If MySQL is not that intelligent and it looks at ALL INDEXES before selecting the “best match” during a search the impact here can be notable as well, though not nearly as dramatic as a updating or inserting new data.

Do You Have A Problem?

Unfortunately there are only 2 ways to tell.

First – your WordPress site starts throwing errors (usually hidden in the error log) that are coming from MySQL and tell you your index limit of 64 entries has been exceeded.    You shouldn’t need to increase this limit, it is per-table and there are very few (if any) cases where 64 indexes on a single table is helpful.

Second – use the MySQL command line tool or phpMyAdmin and look at the wp_store_locator table.   If it has _# indexes they need to go.

The Manual Fix

The way to fix installations by hand is to use the MySQL command line tool, or PHPMyAdmin and drop the _# indexes from the wp_store_locator table.   Since Store Locator Plus is not the only plugin affected you may want to check for any other tables that are created by plugins and see if they suffer from the same issue.  Contact the plugin author before randomly deleting indexes.

From the MySQL command line you can execute the DROP INDEX command as often as necessary based on how many duplicate indexes appear:

mysql> DROP INDEX sl_store_2 ON wp_store_locator;
mysql> DROP INDEX sl_latitude_2 ON wp_store_locator;
mysql> DROP INDEX sl_longitude_2 ON wp_store_locator;

You should leave the indexes without the _# intact.

The Automated Fix

I am looking into ways to detect and auto-delete the secondary indexes in Store Locator Plus in the 3.9.X+ updates, however there is a danger of bloating the update/install process which I want to avoid.    I am also looking for ways to prevent the problem from happening in the first place and have been active with the reported bug to see if there is a way to get WordPress Core developers to patch the problem.

If a workaround is found it will be noted in the updates road map for SLP.

###

Alexa Rank: 264,930 / 89,424 / 611

Technorati Rank: 27347

Tags: , , , , , ,

4 Awesome Comments So Far

Don't be a stranger, join the discussion by leaving your own comment
  1. Janr
    February 27, 2013 at 1:37 PM #

    As I am not quite sure I am looking at what you are talking about. There are many rows in the MySQL table that have #44 – like the one below.

    Tom Roberts, CFP®, MBA A New Approach Financial Planning 1990 Main Street, Suite 750

    Most have the #44 but some have #and another number.

    Am I supposed to delete all the #44′s???

    I was getting ready to add SLP Store Pages also..should I wait?

    Thanks!

    • lcleveland
      February 27, 2013 at 2:11 PM #

      I am not talking about location data.

      I am talking about data table indexes. If you don’t know what an index on a data table is or how to look at them you should not be playing with them. Send this post to your system or database administrator.

      The Store Locator Plus data table and related indexes may have duplicate entries that affect performance. If you don’t notice any performance issues on your site and you are confused by this then you are better off not playing with it or you may break something.

      Show Indexes from MySQL

      • Janr
        February 27, 2013 at 3:40 PM #

        Thanks. I did look at table index and didn’t see any errors there, so looked at data also…as I printed above.

        Other than the map loading slowly, I think it’s ok. Maybe not, and as you point out maybe I am not smart enough to know.

        Maybe there are others not smart enough either. I hope it’s all ok for them too.

        I’m off to purchase more software from you..the Store pages. Thanks for great software!

    • lcleveland
      February 27, 2013 at 3:43 PM #

      Don’t confuse being smart with having knowledge. There are a lot of things I don’t know, it all depends on the subject matter.

      Don’t worry about deleting any data the data is fine.

      When I write “delete any indexes ending with _#” I mean things like “drop index sl_store_1 on store_locator” or “sl_store_2″ or “sl_store_3″, etc. Since that last number can go from 2..n I write _# meaning “any index that ends with _“.

      The reason I approved your comment on the article was so that others that are confused by what I wrote can maybe get some clarification down here in the comments.