Database Error Specified Key Was Too Long
Bobette K. asked 8 years 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...
- 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
- 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:
PHP Warning: Missing argument 2 for wp_update_term_count(), called in /home/yourvirtualwedding/www/products/wp-content/plugins/wpi-performance-plugin/wpi-performance-plugin.php on line 71 and defined in /home/yourvirtualwedding/www/products/wp-includes/taxonomy.php on line 3576, referer: http://www.myonlineweddinghelp.com/products/wp-admin/update.php?action=upload-plugin
PHP Warning: Invalid argument supplied for foreach() in /home/yourvirtualwedding/www/products/wp-content/plugins/wpi-performance-plugin/wpi-performance-plugin.php on line 1306, referer: http://www.myonlineweddinghelp.com/
- PHP Warning: Invalid argument supplied for foreach() in /home/yourvirtualwedding/www/products/wp-content/plugins/wpi-performance-plugin/wpi-performance-plugin.php on line 211, referer: http://www.myonlineweddinghelp.com/products/wp-admin/edit-tags.php?taxonomy=product_cat&post_type=product
Thank you!
Dave H. answered 8 years 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.
Dave H. replied 8 years 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 K. replied 8 years 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.
Bobette K. answered 8 years 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
Bobette K. replied 8 years 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
Bobette K. replied 8 years 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.
Dave H. replied 8 years 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.
Dave H. answered 8 years 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 H. replied 8 years 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/
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.
Just following up to see if you received log-in credentials.
Thanks, Dave. You should be receiving an email with login credentials.
Hi – would you be able to invite me as admin so I can examine your database and figure out why this is happening?