Database Error Specified Key Was Too Long

WP Intense Q & ACategory: Scalability ProDatabase Error Specified Key Was Too Long
Bobette asked 8 months ago

Hi Dave,

I just bought the WPI Performance Plugin and am excited about the possibilities. I installed and have a couple of questions, as I am not sure if it indexed the database…

  1. I have the following database error in my logs. Does this mean the plug in was not able to optimize the database?

PHP message: WordPress database error Specified key was too long; max key length is 1000 bytes for query create index awd_fast_filters_boost7 on products__terms(term_id, name, slug); made by activate_plugin, do_action(‘activate_wpi-performance-plugin/wpi-performance-plugin.php’), call_user_func_array, awdff_activateplugin, referer: http://www.myonlineweddinghelp.com/products/wp-admin/update.php?action=upload-plugin

  1. There are also three PHP warnings. I am wondering if these may be an incompatibility with the Category Order and Taxonomy Terms Order plug-in I have installed (the AWD Category Widget displays, but the categories are not in the order set in the Category Order plug-in).

Here are the warnings:

Thank you!

Dave Hilditch Staff replied 8 months ago

Hi – would you be able to invite me as admin so I can examine your database and figure out why this is happening?

Bobette replied 8 months ago

Thanks, Dave. You should be receiving an email with login credentials.

Bobette replied 8 months ago

Just following up to see if you received log-in credentials.

Bobette replied 8 months ago

Hi Dave, were you able to go in and see if the plugin is working as intended on my site? I have not seen any speed increases.

4 Answers
Dave Hilditch Staff answered 8 months ago

Hi – yes – and I’ve released an upgrade just now (v2.7) which fixes these warning messages. Try it and let me know how it goes.

Bobette replied 8 months ago

Hi Dave. I have installed v2.7 and the PHP warnings are gone. I got two database errors. One the same as before and a second new one:

FastCGI: server “/dev/shm/yourvirtualwedding-php.fcgi” stderr: PHP message: WordPress database error Can’t DROP ‘awd_fast_filters_boost7’; check that column/key exists for query drop index awd_fast_filters_boost7 on products__terms; made by deactivate_plugins, do_action(‘deactivate_wpi-performance-plugin/wpi-performance-plugin.php’), call_user_func_array, awdff_dectivateplugin, QM_DB->query, referer: http://www.myonlineweddinghelp.com/products/wp-admin/plugins.php?puc_update_check_result=no_update&puc_slug=wpi-performance

FastCGI: server “/dev/shm/yourvirtualwedding-php.fcgi” stderr: PHP message: WordPress database error Specified key was too long; max key length is 1000 bytes for query create index wpi_performance_boost7 on products__terms(term_id, name, slug); made by activate_plugin, do_action(‘activate_wpi-performance-plugin/wpi-performance-plugin.php’), call_user_func_array, awdff_activateplugin, QM_DB->query, referer: http://www.myonlineweddinghelp.com/products/wp-admin/update.php?action=upload-plugin

For your FYI, here is some additional info (note, I haven’t changed any settings or done troubleshooting because I’m not sure if the plugin is working due to above errors):

– Searches seem to be the slowest pages for me. Here are three measures on the same search (using Woocommerce product search extension) over time. It slowed down after the upgrade. Search for Wedding Band…
Just before 2.7 install: 1.92 s
Just After 2.7 install: 19.08 s
About 15 minutes after 2.7 install: 21.41 s

– Response time when casually clicking through the store seems to have remained about the same before and after, typically 1-3 seconds.

– The category order in the Fast Layered Category Widget is different from both the Woocommerce category order and the Category Order and Taxonomy Terms Order widget settings. For example, both of the latter begin with Accessories and Jewelry. The Layered Category Widget displays Accessories and DIY Wedding Supplies first.

– Regarding upgrade procedure: The “check for updates” link on the installed plugins dashboard did not work. It reported the 2.5 version was up to date. To upgrade I went to my wpintense account and redownloaded the zip file from my receipt. Uninstalled and deleted 2.5, uploaded the new zip and that did the trick.

Dave Hilditch Staff replied 8 months ago

Hi v2.73 includes a fix for searches – we now provide a full text index. If you install the upgrade and then visit the settings page to create the fulltext index you’ll get great perf on your searches.

Bobette answered 8 months ago

Hi Dave, were you able to go in and see if the plugin is working as intended on my site? I have not seen any speed increases

Dave Hilditch Staff replied 8 months ago

Hi – please upgrade to v2.73 – on the settings page you will find examples of pages that are optimised by the plugin as well as ability to create fulltext index.

Bobette replied 8 months ago

Thank you. I must have an usual WP set up or something…as with the previous update, check for updates says the plug-in is up to date when the v2.7 version is active. I will download the v2.73 zip file to install.

Bobette replied 8 months ago

Completed the upgrade. I love the different settings options and explanations behind each. Knowing the speed tradeoff for different features is definitely helpful. Loving it. Regarding search, it created an index for 13,000 products in what seemed like the blink of an eye. Search is no longer “churning” (yay). One hiccup I noticed: the WPI Price Widget doesn’t seem to be working in combination with the Woocommerce Product Search extension. Here’s an example…the slider is set for 223-601 but showing prices outside that range.
http://www.myonlineweddinghelp.com/products/?s=mischka&post_type=product&tags=0&limit=10&ixwps=1&min_price=223.00&max_price=601.00

Dave Hilditch Staff answered 8 months ago

I’ve fixed the key-length issue you were having and I’ve added conditional code to check if users are using MyISAM tables and to shorten the key on wp_terms if they are.

Also – I removed the unused options for ewww and wpseo_sitemap so that’s speeded up things a bit.

The things still slowing down your site are:

1) Datafeedr has not yet loaded all the images – it is loading these as pages load. You can use the datafeedr tool to bulk import your images if you wish, or you can use my external images plugin to avoid this problem

2) You are using the WooCommerce price filter widget – this is causing pad_termcounts to still be called as well as doing a poor sql query every page

Fix those 2 remaining things and you’ll be flying.

Dave Hilditch Staff replied 8 months ago

Also – if it’s at all possible for you, switch to using a real cron job instead of WP_CRON – http://www.affiliatewebdesigners.com/2014/11/22/set-real-cron-job-datafeedr-woocommerce-product-sets/

Bobette answered 8 months ago

Thank you, Dave! I see a definnite difference now. I will look into the other things you suggested.

Dave Hilditch Staff replied 8 months ago

Check latest v2.73

  • Hullo and welcome! Chat directly to the site owners below.
Latest Message: