Forum

Thread tagged as: Problem, Configuration, Shop

Shop Performance

Hi,

I have been developing a new online shop for a client and I have noticed that as the products have been added the saving, updating, generating variants and editing variants have slowed to a crawl. At times taking up to 30 seconds to save, though averaging around 17-20 seconds.

Not only this but while this action is completing the front of the site is locking up too, it would seem there is some serious blocking when it comes to the database. I am not sure it is the server as the site is hosted on a reasonable powerful VPS with few other sites to contest resources with. Additionally, saving standard content for pages is pretty much instantaneous.

The shop contains around 180 products, with variants resulting in 688 rows on the shop_products table. The shop_index table contains 23,150 rows - would this be considered normal for these numbers? The same locking action happens when saving Brands as well, there are only 6 of those stored.

The response times for reading data from the shop is fast, and seems faster since the 1.0.10 update, this is only occuring when saving a product / brand.

What would you suggest is the reason for the locking up? Do we need to provide additional resources to the shop or is there an error with the shop indexing process to cause this?

Diagnostics:

Diagnostics report
PERCH INFORMATION

Perch Runway: 2.8.32
Production mode: Production (100)
Installed apps: content (2.8.32), assets (2.8.32), categories (2.8.32), root_analytics (1.0.1), perch_forms (1.8.3), perch_shop_orders (1.0.10), perch_shop_products (1.0.10), perch_shop (1.0.10), perch_members (1.5), root_artithmetic (1), root_breadcrumbs (1.0), perch_mailchimp (3.0.1)
DB driver: PDO
DB tables: perch2_backup_plans (0), perch2_backup_resources (0), perch2_backup_runs (0), perch2_categories (29), perch2_category_counts (0), perch2_category_sets (4), perch2_collection_index (0), perch2_collection_items (0), perch2_collection_revisions (0), perch2_collections (0), perch2_content_index (2013), perch2_content_items (252), perch2_content_regions (45), perch2_forms (2), perch2_forms_responses (1), perch2_mailchimp_campaigns (0), perch2_mailchimp_imports (0), perch2_mailchimp_lists (1), perch2_mailchimp_subscribers (1), perch2_mailchimp_subscriptions (1), perch2_mailchimp_webhooks (1), perch2_members (1), perch2_members_forms (1), perch2_members_member_tags (0), perch2_members_sessions (1), perch2_members_tags (0), perch2_navigation (4), perch2_navigation_pages (19), perch2_page_routes (6), perch2_page_templates (28), perch2_pages (32), perch2_resource_log (2011), perch2_resource_tags (8), perch2_resources (1750), perch2_resources_to_tags (64), perch2_root_analytics_cache (3), perch2_scheduled_tasks (11), perch2_settings (37), perch2_shop_addresses (8), perch2_shop_admin_index (126), perch2_shop_brands (6), perch2_shop_cart (2577), perch2_shop_cart_data (36), perch2_shop_cart_items (11), perch2_shop_countries (250), perch2_shop_currencies (152), perch2_shop_customers (1), perch2_shop_emails (2), perch2_shop_index (23150), perch2_shop_option_values (20), perch2_shop_options (1), perch2_shop_order_items (3), perch2_shop_order_promotions (0), perch2_shop_order_statuses (9), perch2_shop_orders (3), perch2_shop_orders_meta (0), perch2_shop_product_files (0), perch2_shop_product_option_values (541), perch2_shop_product_options (125), perch2_shop_product_tags (0), perch2_shop_products (688), perch2_shop_promotions (0), perch2_shop_search (285), perch2_shop_shipping_zone_countries (1), perch2_shop_shipping_zones (2), perch2_shop_shippings (4), perch2_shop_tax_exhibits (6), perch2_shop_tax_group_rates (1), perch2_shop_tax_groups (1), perch2_shop_tax_locations (1), perch2_shop_tax_rates (1), perch2_shop_variants (541), perch2_user_passwords (1), perch2_user_privileges (74), perch2_user_role_privileges (16), perch2_user_roles (2), perch2_users (1)
Users: 1
App runtimes:
<?php
$apps_list = [
    'content',
    'categories',
    'perch_forms',
    'perch_members',
    'perch_shop',
    'perch_mailchimp',
    'root_breadcrumbs',
    'root_arithmetic',
];
Scheduled tasks for perch_mailchimp: import_data (1 mins)
Scheduled tasks for root_analytics: root_analytics_fetch_comparisons (1440 mins)
Editor plug-ins: markitup, redactor
H1: 08bfd0c44a6a60ae38bf35a9268e0a07
L1: 48729459af5dd8f62aba38d76e6c3aa1
F1: 2edba60ed1f613d6dd804feb202456a2
headerColour: #23AE9F
content_singlePageEdit: 1
helpURL: mailto:design@rootstudio.co.uk
siteURL: /
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.8.31: done
perch_shop_update: 1.0.10
headerScheme: dark
update_runway_2.8.31: done
latest_version: 2.8.15
on_sale_version: 2.8.32
dashboard: 1
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
logoPath: /admin/resources/little-puds-and-co-logo-with-stars.png
perch_members_login_page: /account?r={returnURL}
perch_shop_price_tax_mode: exc
perch_shop_trade_price_tax_mode: exc
perch_shop_site_url: 
perch_shop_product_url: /shop/product/{slug}
perch_shop_default_currency: 47
perch_shop_reporting_currency: 47
perch_shop_invoice_number_format: Order #%d
perch_members_update: 1.4
update_runway_2.8.32: done
perch_mailchimp_api_key: 
perch_mailchimp_campaign_url: /mailchimp/campaign/{campaignSlug}
perch_mailchimp_secret: 20e37ed
root_analytics_namespace: Little Puds & Co
root_analytics_view_id: 
root_analytics_credentials_json: 
root_analytics_time_period: 90
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /home/suzanne/public_html
PERCH_DB_USERNAME: suzanne_perchu
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: suzanne_perch_shop
PERCH_DB_PREFIX: perch2_
PERCH_PRODUCTION_MODE: 100
PERCH_DEBUG:
PERCH_EMAIL_FROM: james@rootstudio.co.uk
PERCH_EMAIL_FROM_NAME: Root Studio
PERCH_LOGINPATH: /admin
PERCH_PATH: /home/suzanne/public_html/admin
PERCH_CORE: /home/suzanne/public_html/admin/core
PERCH_RESFILEPATH: /home/suzanne/public_html/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_TZ: UTC
PERCH_RUNWAY: 1
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_RUNWAY_ROUTED:
PERCH_STRONG_PASSWORDS:
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /home/suzanne/public_html/admin/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_RWD:
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_SESSION_TIMEOUT_MINS: 20
PERCH_SHOP_VERSION: 1.0.10
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: textile
HOSTING SETTINGS

PHP: 5.6.26
Zend: 2.6.0
OS: Linux
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
MySQL server: 5.6.34
Free disk space: 80.76 GB
Extensions: Core, date, ereg, libxml, openssl, pcre, sqlite3, zlib, bcmath, calendar, ctype, curl, dom, fileinfo, filter, ftp, gd, hash, iconv, SPL, intl, json, mbstring, mcrypt, session, standard, mysqlnd, Phar, posix, Reflection, mysql, SimpleXML, sockets, imap, tokenizer, xml, xmlreader, xmlwriter, cgi-fcgi, PDO, pdo_sqlite, imagick, pdo_mysql
GD: Yes
ImageMagick: Yes
PHP max upload size: 200M
PHP max form post size: 12M
PHP memory limit: 256M
Total max uploadable file size: 12M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
CONTEXT_DOCUMENT_ROOT: /home/suzanne/public_html
DOCUMENT_ROOT: /home/suzanne/public_html
GATEWAY_INTERFACE: CGI/1.1
HTTPS: on
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_ACCEPT_LANGUAGE: en-gb
HTTP_CONNECTION: keep-alive
HTTP_COOKIE: _ga=GA1.3.875184230.1479730398; PHPSESSID=64381520a7e050bd454cbf14252dff6d; cpsession=suzanne%3aFd4M1qUYgGPv7tJr%2ccd6028ead345fbfbf92e531688a7fa02; timezone=Europe/London; cmsa=1; cmssb=0; p_m=856c543c74da4490b1c4c0441724ca47c9395c7d; cookieconsent_dismissed=yes
HTTP_HOST: 
HTTP_REFERER: 
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12) AppleWebKit/602.1.50 (KHTML, like Gecko) Version/10.0 Safari/602.1.50
PATH: /bin:/usr/bin
QUERY_STRING: extended
REDIRECT_STATUS: 200
REMOTE_ADDR: 81.130.131.129
REMOTE_PORT: 51476
REQUEST_METHOD: GET
REQUEST_SCHEME: https
REQUEST_URI: /admin/core/settings/diagnostics/?extended
SCRIPT_FILENAME: /home/suzanne/public_html/admin/core/settings/diagnostics/index.php
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
SERVER_ADDR: 162.251.83.56
SERVER_ADMIN: 
SERVER_NAME: 
SERVER_PORT: 443
SERVER_PROTOCOL: HTTP/1.1
SERVER_SOFTWARE: Apache
SSL_TLS_SNI: 
UNIQUE_ID: 
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1479745901.66
REQUEST_TIME: 1479745901
argc: 1

Additionally, here are some debug logs of saving brands:

SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='a4b630e3a7be45f4fd83235ef083966b' LIMIT 1
UPDATE perch2_users SET userHash='a096f684a671220f6bf7632adb558a67' WHERE userID='1'
[74] SELECT p.privKey FROM perch2_user_privileges p
[37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
Adding: Arithmetic
Adding: Breadcrumbs
[nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
[1] SELECT * FROM perch2_shop_brands WHERE brandID='6' AND brandDeleted IS NULL LIMIT 1
Using template: /templates/shop/brands/brand.html
[1] SELECT * FROM perch2_resources WHERE resourceID='98' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceTargetWidth=361 AND resourceTargetHeight=361 AND resourceCrop=0 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='98' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=0 AND resourceDensity=1
Logging resources for perch_shop
DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='brandID' AND itemRowID=6
INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','brandID',6,98),('perch_shop','brandID',6,99),('perch_shop','brandID',6,100),('perch_shop','brandID',6,101)
UPDATE perch2_shop_brands SET brandDynamicFields='{\"title\":\"Powell Craft\",\"_title\":\"Powell Craft\",\"slug\":\"powell-craft\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just adorable and excellent quality.<\\/p>\",\"processed\":\"<p>Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just adorable and excellent quality.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"\",\"processed\":\"\"},\"url\":null,\"image\":{\"assetID\":\"98\",\"title\":\"Powell craft\",\"_default\":\"\\/admin\\/resources\\/brands\\/powell-craft.png\",\"bucket\":\"brands\",\"path\":\"powell-craft.png\",\"size\":47991,\"w\":512,\"h\":512,\"mime\":\"image\\/png\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"powell-craft-thumb@2x.png\",\"size\":51669,\"mime\":\"\",\"assetID\":\"99\"},\"w361h361c0\":{\"w\":\"361\",\"h\":\"361\",\"target_w\":\"361\",\"target_h\":\"361\",\"crop\":false,\"density\":\"1\",\"path\":\"powell-craft-w361h361.png\",\"size\":72333,\"mime\":\"\",\"assetID\":\"100\"},\"w512h512c0\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":false,\"density\":\"1\",\"path\":\"powell-craft-w512h512.png\",\"size\":47991,\"mime\":\"\",\"assetID\":\"101\"}}},\"alt_text\":\"Powell craft\",\"status\":\"1\"}', brandTitle='Powell Craft', brandUpdated='2016-11-21 16:12:41' WHERE brandID='6'
No ids to log.
DELETE FROM perch2_shop_index WHERE itemKey='brandID' AND itemID='6'
INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('brandID',6,'title','Powell Craft'),('brandID',6,'_title','Powell Craft'),('brandID',6,'slug','powell-craft'),('brandID',6,'description','Powell Craft Ltd has been a family-run business since 1962. The product range has grown and diversified over the years. The last decade has seen the launch of a wide selection of childrenswear & babywear inspired by using vintage prints and fabrics. Just '),('brandID',6,'meta_description',''),('brandID',6,'url',''),('brandID',6,'image','/admin/resources/brands/powell-craft.png'),('brandID',6,'alt_text','Powell craft'),('brandID',6,'status','1'),('brandID',6,'brandID','6'),('brandID',6,'brandTitle','Powell Craft'),('brandID',6,'brandCreated','2016-09-23 09:39:14'),('brandID',6,'brandUpdated','2016-11-21 16:12:41'),('brandID',6,'brandDeleted',''),('brandID',6,'itemID',''),('brandID',6,'itemRowID',''),('brandID',6,'_id',6)
[1] OPTIMIZE TABLE perch2_shop_index
Queries: 20
Memory: 5.3238

and saving a product

SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='9487bab867a687a180b8e54a3f34f46a' LIMIT 1
UPDATE perch2_users SET userHash='20ddcdfe5c4126e6a6ed69dba31b2447' WHERE userID='1'
[74] SELECT p.privKey FROM perch2_user_privileges p
[37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
Adding: Arithmetic
Adding: Breadcrumbs
[nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
[1] SELECT * FROM perch2_shop_products WHERE productID='167' AND productDeleted IS NULL LIMIT 1
Using template: /templates/shop/products/product.html
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[3] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='167' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch2_resources WHERE resourceID='535' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=200 AND resourceTargetHeight=200 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=58 AND resourceTargetHeight=58 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceID='535' LIMIT 1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceKey='thumb'
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=415 AND resourceTargetHeight=415 AND resourceCrop=1 AND resourceDensity=1
[1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='535' AND resourceTargetWidth=1024 AND resourceTargetHeight=1024 AND resourceCrop=0 AND resourceDensity=1
[1] SELECT * FROM perch2_categories WHERE catID=6 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=55 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=25 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
Logging resources for perch_shop
DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='productID' AND itemRowID=167
INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','productID',167,535),('perch_shop','productID',167,536),('perch_shop','productID',167,537),('perch_shop','productID',167,538),('perch_shop','productID',167,539),('perch_shop','productID',167,535),('perch_shop','productID',167,536),('perch_shop','productID',167,540),('perch_shop','productID',167,541)
[3] SELECT * FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='168'
No ids to log.
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='169'
No ids to log.
UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\"}', productStockOnParent='0', productUpdated='2016-11-21 16:14:40' WHERE productID='170'
No ids to log.
UPDATE perch2_shop_products SET title='Racing Car Leggings', sku='BR0026', stock_level=NULL, productDynamicFields='{\"_title\":\"Racing Car Leggings\",\"slug\":\"racing-car-leggings-br0026\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling!<\\/p>\",\"processed\":\"<p>The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling!<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!\",\"processed\":\"Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!\"},\"favourite\":null,\"product_image_thumb\":{\"assetID\":\"535\",\"title\":\"Racing Car Leggings\",\"_default\":\"\\/admin\\/resources\\/shop\\/racing-car-leggings.jpg\",\"bucket\":\"shop\",\"path\":\"racing-car-leggings.jpg\",\"size\":2729552,\"w\":2000,\"h\":1333,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"99\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"racing-car-leggings-thumb@2x.jpg\",\"size\":18484,\"mime\":\"\",\"assetID\":\"536\"},\"w200h200c1\":{\"w\":\"200\",\"h\":\"200\",\"target_w\":\"200\",\"target_h\":\"200\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w200h200.jpg\",\"size\":13991,\"mime\":\"\",\"assetID\":\"537\"},\"w512h512c1\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w512h512.jpg\",\"size\":75543,\"mime\":\"\",\"assetID\":\"538\"},\"w58h58c1\":{\"w\":\"58\",\"h\":\"58\",\"target_w\":\"58\",\"target_h\":\"58\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w58h58.jpg\",\"size\":2111,\"mime\":\"\",\"assetID\":\"539\"}}},\"product_image_thumb_alt\":null,\"product_images\":[{\"product_image_main\":{\"assetID\":\"535\",\"title\":\"Racing Car Leggings\",\"_default\":\"\\/admin\\/resources\\/shop\\/racing-car-leggings.jpg\",\"bucket\":\"shop\",\"path\":\"racing-car-leggings.jpg\",\"size\":2729552,\"w\":2000,\"h\":1333,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"99\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"racing-car-leggings-thumb@2x.jpg\",\"size\":18484,\"mime\":\"\",\"assetID\":\"536\"},\"w415h415c1\":{\"w\":\"415\",\"h\":\"415\",\"target_w\":\"415\",\"target_h\":\"415\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"racing-car-leggings-w415h415.jpg\",\"size\":51831,\"mime\":\"\",\"assetID\":\"540\"},\"w1024h1024c0\":{\"w\":\"1024\",\"h\":\"682\",\"target_w\":\"1024\",\"target_h\":\"1024\",\"crop\":false,\"density\":\"1\",\"path\":\"racing-car-leggings-w1024h1024.jpg\",\"size\":185760,\"mime\":\"\",\"assetID\":\"541\"}}},\"product_image_caption\":null}],\"brand\":\"2\",\"range\":[\"6\"],\"age_size\":[\"55\",\"56\"],\"category\":[\"25\"],\"special\":null,\"price\":{\"47\":\"10.00\",\"_default\":\"10.00\"},\"sale_price\":{\"47\":\"\",\"_default\":\"\"},\"trade_price\":{\"47\":\"\",\"_default\":\"\"},\"on_sale\":null,\"tax_group\":\"1\",\"stock_status\":\"0\",\"stock_location\":\"0\",\"max_in_cart\":null,\"requires_shipping\":\"1\",\"weight\":null,\"width\":null,\"height\":null,\"depth\":null,\"catalog_only\":\"0\",\"status\":\"1\"}', productStockOnParent='0', productSlug='racing-car-leggings-br0026', productUpdated='2016-11-21 16:14:40' WHERE productID='167'
No ids to log.
DELETE FROM perch2_shop_index WHERE itemKey='productID' AND itemID='167'
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[3] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='167' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch2_shop_brands WHERE brandID='2' AND brandDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=6 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=55 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
[1] SELECT * FROM perch2_categories WHERE catID=25 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
[1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch2_shop_tax_groups WHERE groupID='1' AND groupDeleted IS NULL LIMIT 1
INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('productID',167,'_title','Racing Car Leggings'),('productID',167,'slug','racing-car-leggings-br0026'),('productID',167,'description','The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool'),('productID',167,'meta_description','Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers!'),('productID',167,'favourite',''),('productID',167,'product_image_thumb','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_image_thumb_alt',''),('productID',167,'product_images.product_image_main','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_images.product_image_main','/admin/resources/shop/racing-car-leggings.jpg'),('productID',167,'product_images.product_image_caption',''),('productID',167,'brand','2'),('productID',167,'brand.title','Blade & Rose'),('productID',167,'brand._title','Blade & Rose'),('productID',167,'brand.slug','blade-rose'),('productID',167,'brand.url',''),('productID',167,'brand.alt_text','Blade & Rose logo'),('productID',167,'brand.status','1'),('productID',167,'brand.brandID','2'),('productID',167,'brand.brandTitle','Blade & Rose'),('productID',167,'brand.brandCreated','2016-09-23 09:38:01'),('productID',167,'brand.brandUpdated','2016-11-21 16:09:45'),('productID',167,'brand.brandDeleted',''),('productID',167,'_category','ranges/boys/'),('productID',167,'_category','age-size/6-12-months/'),('productID',167,'_category','age-size/1-2-years/'),('productID',167,'_category','products/leggings/'),('productID',167,'price','10.00'),('productID',167,'price.gbp','10.00'),('productID',167,'sale_price',''),('productID',167,'sale_price.gbp',''),('productID',167,'trade_price',''),('productID',167,'trade_price.gbp',''),('productID',167,'on_sale',''),('productID',167,'tax_group','1'),('productID',167,'tax_group.groupID','1'),('productID',167,'tax_group.groupTitle','Default'),('productID',167,'tax_group.groupSlug','default'),('productID',167,'tax_group.groupTaxRate','buyer'),('productID',167,'tax_group.groupCreated','2016-09-23 09:34:09'),('productID',167,'tax_group.groupUpdated',''),('productID',167,'tax_group.groupDeleted',''),('productID',167,'stock_status','0'),('productID',167,'stock_location','0'),('productID',167,'max_in_cart',''),('productID',167,'requires_shipping','1'),('productID',167,'weight',''),('productID',167,'width',''),('productID',167,'height',''),('productID',167,'depth',''),('productID',167,'catalog_only','0'),('productID',167,'status','1'),('productID',167,'productID','167'),('productID',167,'title','Racing Car Leggings'),('productID',167,'sku','BR0026'),('productID',167,'productSlug','racing-car-leggings-br0026'),('productID',167,'stock_level',''),('productID',167,'parentID',''),('productID',167,'productVariantDesc',''),('productID',167,'productOrder','1'),('productID',167,'productHasVariants','1'),('productID',167,'productStockOnParent','0'),('productID',167,'productTemplate','product.html'),('productID',167,'productCreated','2016-10-10 10:34:58'),('productID',167,'productUpdated','2016-11-21 16:14:40'),('productID',167,'productDeleted',''),('productID',167,'itemID',''),('productID',167,'itemRowID',''),('productID',167,'has_variants','1'),('productID',167,'_variant_opts','0-6 Months|168,6-12 Months|169,1-2 Years|170'),('productID',167,'_id',167)
[1] OPTIMIZE TABLE perch2_shop_index
DELETE FROM perch2_shop_search WHERE itemKey=167 AND itemType='product'
INSERT INTO perch2_shop_search(itemKey,itemType,searchBody) VALUES(167,'product',' BR0026 Racing Car Leggings racing car leggings br0026 The perfect little leggings for budding racing car drivers! These fab leggings are made from 75% combed cotton, 20% nylon and 5% spandex for a bit of stretchiness. They feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom which is perfect for showing off when little ones are zooming about crawling! Feature racing car red and navy stripes on the legs with stars on the knees and a cool racing car design on the bottom. Perfect for budding racing car drivers! 2 Boys 6-12 Months, 1-2 Years Leggings 1 0 0 1 0 1 ')
[3] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=167 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=167 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[3] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='167' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[6] SELECT * FROM perch2_shop_brands WHERE 1=1 AND brandDeleted IS NULL ORDER BY brandTitle ASC
[4] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='ranges' ORDER BY catTreePosition ASC
[6] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='age-size' ORDER BY catTreePosition ASC
[17] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='products' ORDER BY catTreePosition ASC
[2] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='specials' ORDER BY catTreePosition ASC
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
[1] SELECT * FROM perch2_shop_tax_groups WHERE 1=1 AND groupDeleted IS NULL ORDER BY groupTitle ASC
James Wigger

James Wigger 0 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

Do you see the same characteristics in your dev environment? If so, could you switch into dev mode so that the debug has timing output?

Hi Drew,

In my local environment the length of time spent waiting for the save actions to complete is less, but there is still a noticeable wait for it to complete. Here are the timed logs for local:

Saving a brand:

Time    Δ   Debug Message
0.0129  0   [1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='2cacabe4692b09552261c8185281238a' LIMIT 1
0.0135  0.0006  UPDATE perch2_users SET userHash='394a1945b830dbd12b0897404f82de20' WHERE userID='1'
0.0138  0.0003  [74] SELECT p.privKey FROM perch2_user_privileges p
0.0155  0.0017  [37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
0.0406  0.0251  Adding: Arithmetic
0.0408  0.0002  Adding: Breadcrumbs
0.0409  0.0001  [nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
0.0451  0.0042  [1] SELECT * FROM perch2_shop_brands WHERE brandID='2' AND brandDeleted IS NULL LIMIT 1
0.0491  0.004   Using template: /templates/shop/brands/brand.html
0.0761  0.027   [1] SELECT * FROM perch2_resources WHERE resourceID='78' LIMIT 1
0.1032  0.0271  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceKey='thumb'
0.105   0.0018  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceTargetWidth=361 AND resourceTargetHeight=361 AND resourceCrop=0 AND resourceDensity=1
0.1056  0.0007  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=0 AND resourceDensity=1
0.1065  0.0008  Logging resources for perch_shop
0.1065  0.0001  DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='brandID' AND itemRowID=2
0.1607  0.0542  INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','brandID',2,78),('perch_shop','brandID',2,79),('perch_shop','brandID',2,80),('perch_shop','brandID',2,94)
0.1621  0.0014  UPDATE perch2_shop_brands SET brandDynamicFields='{\"title\":\"Blade & Rose\",\"_title\":\"Blade & Rose\",\"slug\":\"blade-rose\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Blade & Rose is a leading British children\\u2019s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the development was to ensure that the quality of the fabric was durable, functional, would wash well and most importantly, that was soft and kind to children\\u2019s delicate skin.<\\/p>\",\"processed\":\"<p>Blade & Rose is a leading British children\\u2019s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the development was to ensure that the quality of the fabric was durable, functional, would wash well and most importantly, that was soft and kind to children\\u2019s delicate skin.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"\",\"processed\":\"\"},\"url\":\"https:\\/\\/www.bladeandrose.co.uk\",\"image\":{\"assetID\":\"78\",\"title\":\"Blade and rose\",\"_default\":\"\\/admin\\/resources\\/brands\\/blade-and-rose.png\",\"bucket\":\"brands\",\"path\":\"blade-and-rose.png\",\"size\":28119,\"w\":512,\"h\":512,\"mime\":\"image\\/png\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"blade-and-rose-thumb@2x.png\",\"size\":34637,\"mime\":\"\",\"assetID\":\"79\"},\"w361h361c0\":{\"w\":\"361\",\"h\":\"361\",\"target_w\":\"361\",\"target_h\":\"361\",\"crop\":false,\"density\":\"1\",\"path\":\"blade-and-rose-w361h361.png\",\"size\":50580,\"mime\":\"\",\"assetID\":\"80\"},\"w512h512c0\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":false,\"density\":\"1\",\"path\":\"blade-and-rose-w512h512.png\",\"size\":28119,\"mime\":\"\",\"assetID\":\"94\"}}},\"alt_text\":\"Blade & Rose logo\",\"status\":\"1\"}', brandTitle='Blade & Rose', brandUpdated='2016-11-22 09:08:53' WHERE brandID='2'
0.1643  0.0021  No ids to log.
0.1644  0.0001  DELETE FROM perch2_shop_index WHERE itemKey='brandID' AND itemID='2'
0.1775  0.0131  INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('brandID',2,'title','Blade & Rose'),('brandID',2,'_title','Blade & Rose'),('brandID',2,'slug','blade-rose'),('brandID',2,'description','Blade & Rose is a leading British children’s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the developm'),('brandID',2,'meta_description',''),('brandID',2,'url','https://www.bladeandrose.co.uk'),('brandID',2,'image','/admin/resources/brands/blade-and-rose.png'),('brandID',2,'alt_text','Blade & Rose logo'),('brandID',2,'status','1'),('brandID',2,'brandID','2'),('brandID',2,'brandTitle','Blade & Rose'),('brandID',2,'brandCreated','2016-09-23 09:38:01'),('brandID',2,'brandUpdated','2016-11-22 09:08:53'),('brandID',2,'brandDeleted',''),('brandID',2,'itemID',''),('brandID',2,'itemRowID',''),('brandID',2,'_id',2)
0.1967  0.0192  [1] OPTIMIZE TABLE perch2_shop_index
3.3231  3.1264  Queries: 20
3.3231  0   Memory: 6.325

Saving a product:

Time    Δ   Debug Message
0.0135  0   [1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='21c577bf7b58506ecbabbd17f31f1ece' LIMIT 1
0.0142  0.0006  UPDATE perch2_users SET userHash='f6b504af90b471228dccfe1cc8138f86' WHERE userID='1'
0.0145  0.0003  [74] SELECT p.privKey FROM perch2_user_privileges p
0.0162  0.0017  [37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
0.0402  0.0241  Adding: Arithmetic
0.0404  0.0002  Adding: Breadcrumbs
0.0406  0.0001  [nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
0.0464  0.0058  [1] SELECT * FROM perch2_shop_products WHERE productID='11' AND productDeleted IS NULL LIMIT 1
0.0509  0.0045  Using template: /templates/shop/products/product.html
0.0683  0.0174  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
0.0696  0.0013  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.0712  0.0015  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.0717  0.0005  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
0.0815  0.0098  [1] SELECT * FROM perch2_resources WHERE resourceID='124' LIMIT 1
0.1016  0.0202  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceKey='thumb'
0.1027  0.001   [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=200 AND resourceTargetHeight=200 AND resourceCrop=1 AND resourceDensity=1
0.1035  0.0009  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=1 AND resourceDensity=1
0.1043  0.0008  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=58 AND resourceTargetHeight=58 AND resourceCrop=1 AND resourceDensity=1
0.1069  0.0026  [1] SELECT * FROM perch2_resources WHERE resourceID='124' LIMIT 1
0.1075  0.0006  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceKey='thumb'
0.1082  0.0007  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=415 AND resourceTargetHeight=415 AND resourceCrop=1 AND resourceDensity=1
0.1088  0.0006  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=1024 AND resourceTargetHeight=1024 AND resourceCrop=0 AND resourceDensity=1
0.1108  0.002   [1] SELECT * FROM perch2_categories WHERE catID=7 LIMIT 1
0.1112  0.0004  [1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
0.1115  0.0003  [1] SELECT * FROM perch2_categories WHERE catID=57 LIMIT 1
0.1119  0.0004  [1] SELECT * FROM perch2_categories WHERE catID=21 LIMIT 1
0.1129  0.001   [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.114   0.0011  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.1147  0.0007  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.1163  0.0016  Logging resources for perch_shop
0.1164  0.0001  DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='productID' AND itemRowID=11
0.1416  0.0252  INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','productID',11,124),('perch_shop','productID',11,125),('perch_shop','productID',11,126),('perch_shop','productID',11,127),('perch_shop','productID',11,128),('perch_shop','productID',11,124),('perch_shop','productID',11,125),('perch_shop','productID',11,129),('perch_shop','productID',11,130)
0.1428  0.0012  [4] SELECT * FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL
0.1436  0.0008  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:09:12' WHERE productID='12'
0.1445  0.0009  No ids to log.
0.1447  0.0002  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:09:12' WHERE productID='13'
0.1458  0.0011  No ids to log.
0.146   0.0002  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:09:12' WHERE productID='14'
0.1468  0.0007  No ids to log.
0.1469  0.0002  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:09:12' WHERE productID='15'
0.1477  0.0008  No ids to log.
0.1482  0.0005  UPDATE perch2_shop_products SET title='Ballerina Raincoat', sku='PC0003', stock_level=NULL, productDynamicFields='{\"_title\":\"Ballerina Raincoat\",\"slug\":\"ballerina-raincoat-pc0003\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.<\\/p>\",\"processed\":\"<p>Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.\",\"processed\":\"Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.\"},\"favourite\":null,\"product_image_thumb\":{\"assetID\":\"124\",\"title\":\"Ballerina Raincoat\",\"_default\":\"\\/admin\\/resources\\/shop\\/ballerina-raincoat.jpg\",\"bucket\":\"shop\",\"path\":\"ballerina-raincoat.jpg\",\"size\":455835,\"w\":1000,\"h\":1000,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"ballerina-raincoat-thumb@2x.jpg\",\"size\":15018,\"mime\":\"\",\"assetID\":\"125\"},\"w200h200c1\":{\"w\":\"200\",\"h\":\"200\",\"target_w\":\"200\",\"target_h\":\"200\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w200h200.jpg\",\"size\":7916,\"mime\":\"\",\"assetID\":\"126\"},\"w512h512c1\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w512h512.jpg\",\"size\":34636,\"mime\":\"\",\"assetID\":\"127\"},\"w58h58c1\":{\"w\":\"58\",\"h\":\"58\",\"target_w\":\"58\",\"target_h\":\"58\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w58h58.jpg\",\"size\":1628,\"mime\":\"\",\"assetID\":\"128\"}}},\"product_image_thumb_alt\":null,\"product_images\":[{\"product_image_main\":{\"assetID\":\"124\",\"title\":\"Ballerina Raincoat\",\"_default\":\"\\/admin\\/resources\\/shop\\/ballerina-raincoat.jpg\",\"bucket\":\"shop\",\"path\":\"ballerina-raincoat.jpg\",\"size\":455835,\"w\":1000,\"h\":1000,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"ballerina-raincoat-thumb@2x.jpg\",\"size\":15018,\"mime\":\"\",\"assetID\":\"125\"},\"w415h415c1\":{\"w\":\"415\",\"h\":\"415\",\"target_w\":\"415\",\"target_h\":\"415\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w415h415.jpg\",\"size\":25694,\"mime\":\"\",\"assetID\":\"129\"},\"w1024h1024c0\":{\"w\":\"1000\",\"h\":\"1000\",\"target_w\":\"1024\",\"target_h\":\"1024\",\"crop\":false,\"density\":\"1\",\"path\":\"ballerina-raincoat-w1024h1024.jpg\",\"size\":455835,\"mime\":\"\",\"assetID\":\"130\"}}},\"product_image_caption\":null}],\"brand\":\"6\",\"range\":[\"7\"],\"age_size\":[\"56\",\"57\"],\"category\":[\"21\"],\"special\":null,\"price\":{\"47\":\"24.99\",\"_default\":\"24.99\"},\"sale_price\":{\"47\":\"\",\"_default\":\"\"},\"trade_price\":{\"47\":\"\",\"_default\":\"\"},\"on_sale\":null,\"tax_group\":\"1\",\"stock_status\":\"0\",\"stock_location\":\"0\",\"max_in_cart\":null,\"requires_shipping\":\"1\",\"weight\":null,\"width\":null,\"height\":null,\"depth\":null,\"catalog_only\":\"0\",\"status\":\"1\"}', productStockOnParent='0', productSlug='ballerina-raincoat-pc0003', productStatus='1', productUpdated='2016-11-22 09:09:12' WHERE productID='11'
0.1494  0.0012  No ids to log.
0.1496  0.0002  DELETE FROM perch2_shop_index WHERE itemKey='productID' AND itemID='11'
0.1602  0.0105  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
0.1612  0.0011  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.1619  0.0007  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.1625  0.0006  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
0.1663  0.0038  [1] SELECT * FROM perch2_shop_brands WHERE brandID='6' AND brandDeleted IS NULL LIMIT 1
0.1673  0.0011  [1] SELECT * FROM perch2_categories WHERE catID=7 LIMIT 1
0.1678  0.0005  [1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
0.1681  0.0003  [1] SELECT * FROM perch2_categories WHERE catID=57 LIMIT 1
0.1686  0.0004  [1] SELECT * FROM perch2_categories WHERE catID=21 LIMIT 1
0.169   0.0005  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.1695  0.0005  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.1699  0.0004  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.1703  0.0004  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.1707  0.0004  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.171   0.0004  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.172   0.0009  [1] SELECT * FROM perch2_shop_tax_groups WHERE groupID='1' AND groupDeleted IS NULL LIMIT 1
0.175   0.003   INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('productID',11,'_title','Ballerina Raincoat'),('productID',11,'slug','ballerina-raincoat-pc0003'),('productID',11,'description','Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.'),('productID',11,'meta_description','Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.'),('productID',11,'favourite',''),('productID',11,'product_image_thumb','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_image_thumb_alt',''),('productID',11,'product_images.product_image_main','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_images.product_image_main','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_images.product_image_caption',''),('productID',11,'brand','6'),('productID',11,'brand.title','Powell Craft'),('productID',11,'brand._title','Powell Craft'),('productID',11,'brand.slug','powell-craft'),('productID',11,'brand.url','https://powellcraftboutique.com'),('productID',11,'brand.alt_text','Powell craft'),('productID',11,'brand.status','1'),('productID',11,'brand.brandID','6'),('productID',11,'brand.brandTitle','Powell Craft'),('productID',11,'brand.brandCreated','2016-09-23 09:39:14'),('productID',11,'brand.brandUpdated','2016-10-05 09:29:23'),('productID',11,'brand.brandDeleted',''),('productID',11,'_category','ranges/girls/'),('productID',11,'_category','age-size/1-2-years/'),('productID',11,'_category','age-size/3-4-years/'),('productID',11,'_category','products/rain-coats/'),('productID',11,'price','24.99'),('productID',11,'price.gbp','24.99'),('productID',11,'sale_price',''),('productID',11,'sale_price.gbp',''),('productID',11,'trade_price',''),('productID',11,'trade_price.gbp',''),('productID',11,'on_sale',''),('productID',11,'tax_group','1'),('productID',11,'tax_group.groupID','1'),('productID',11,'tax_group.groupTitle','Default'),('productID',11,'tax_group.groupSlug','default'),('productID',11,'tax_group.groupTaxRate','buyer'),('productID',11,'tax_group.groupCreated','2016-09-23 09:34:09'),('productID',11,'tax_group.groupUpdated',''),('productID',11,'tax_group.groupDeleted',''),('productID',11,'stock_status','0'),('productID',11,'stock_location','0'),('productID',11,'max_in_cart',''),('productID',11,'requires_shipping','1'),('productID',11,'weight',''),('productID',11,'width',''),('productID',11,'height',''),('productID',11,'depth',''),('productID',11,'catalog_only','0'),('productID',11,'status','1'),('productID',11,'productID','11'),('productID',11,'title','Ballerina Raincoat'),('productID',11,'sku','PC0003'),('productID',11,'productSlug','ballerina-raincoat-pc0003'),('productID',11,'stock_level',''),('productID',11,'parentID',''),('productID',11,'productVariantDesc',''),('productID',11,'productOrder','1'),('productID',11,'productHasVariants','1'),('productID',11,'productStockOnParent','0'),('productID',11,'productTemplate','product.html'),('productID',11,'productCreated','2016-10-07 12:24:48'),('productID',11,'productUpdated','2016-11-22 09:09:12'),('productID',11,'productDeleted',''),('productID',11,'productStatus','1'),('productID',11,'itemID',''),('productID',11,'itemRowID',''),('productID',11,'has_variants','1'),('productID',11,'_variant_opts','1-2 Years|12,2-3 Years|13,4/5 Years|14,6/7 Years|15'),('productID',11,'_id',11)
0.2708  0.0958  [1] OPTIMIZE TABLE perch2_shop_index
3.2874  3.0166  DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'
3.3004  0.0131  INSERT INTO perch2_shop_search(itemKey,itemType,searchBody) VALUES(11,'product',' PC0003 Ballerina Raincoat ballerina raincoat pc0003 Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps. Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps. 6 Girls 1-2 Years, 3-4 Years Rain Coats 1 0 0 1 0 1 ')
3.3014  0.001   [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
3.3024  0.001   [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
3.3029  0.0005  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
3.3033  0.0005  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
3.3136  0.0102  [6] SELECT * FROM perch2_shop_brands WHERE 1=1 AND brandDeleted IS NULL ORDER BY brandTitle ASC
3.315   0.0014  [4] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='ranges' ORDER BY catTreePosition ASC
3.318   0.003   [6] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='age-size' ORDER BY catTreePosition ASC
3.3205  0.0025  [17] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='products' ORDER BY catTreePosition ASC
3.3249  0.0044  [2] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='specials' ORDER BY catTreePosition ASC
3.3277  0.0028  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
3.3293  0.0017  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
3.3308  0.0015  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
3.3323  0.0015  [1] SELECT * FROM perch2_shop_tax_groups WHERE 1=1 AND groupDeleted IS NULL ORDER BY groupTitle ASC
3.3391  0.0068  Queries: 77
3.3391  0.0001  Memory: 7.205

This query is flagging up as the longest: DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'

In the production environment the same pattern, though with longer times:

Brand:

Time    Δ   Debug Message
0.0137  0   [1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='60bf308e6fd5b1722255e0f30bc34401' LIMIT 1
0.0142  0.0006  UPDATE perch2_users SET userHash='f5acc5e9b8be7b2a4ae150d4498943b6' WHERE userID='1'
0.0145  0.0003  [74] SELECT p.privKey FROM perch2_user_privileges p
0.0162  0.0017  [37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
0.0222  0.006   Adding: Arithmetic
0.0382  0.016   Adding: Breadcrumbs
0.039   0.0008  [nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
0.0426  0.0037  [1] SELECT * FROM perch2_shop_brands WHERE brandID='2' AND brandDeleted IS NULL LIMIT 1
0.0477  0.005   Using template: /templates/shop/brands/brand.html
0.0672  0.0196  [1] SELECT * FROM perch2_resources WHERE resourceID='78' LIMIT 1
0.0912  0.0239  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceKey='thumb'
0.1954  0.1042  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceTargetWidth=361 AND resourceTargetHeight=361 AND resourceCrop=0 AND resourceDensity=1
0.1965  0.0011  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='78' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=0 AND resourceDensity=1
0.1976  0.0011  Logging resources for perch_shop
0.1977  0.0001  DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='brandID' AND itemRowID=2
0.3437  0.146   INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','brandID',2,78),('perch_shop','brandID',2,79),('perch_shop','brandID',2,80),('perch_shop','brandID',2,94)
0.3444  0.0007  UPDATE perch2_shop_brands SET brandDynamicFields='{\"title\":\"Blade & Rose\",\"_title\":\"Blade & Rose\",\"slug\":\"blade-rose\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Blade & Rose is a leading British children\\u2019s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the development was to ensure that the quality of the fabric was durable, functional, would wash well and most importantly, that was soft and kind to children\\u2019s delicate skin.<\\/p>\",\"processed\":\"<p>Blade & Rose is a leading British children\\u2019s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the development was to ensure that the quality of the fabric was durable, functional, would wash well and most importantly, that was soft and kind to children\\u2019s delicate skin.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"\",\"processed\":\"\"},\"url\":null,\"image\":{\"assetID\":\"78\",\"title\":\"Blade and rose\",\"_default\":\"\\/admin\\/resources\\/brands\\/blade-and-rose.png\",\"bucket\":\"brands\",\"path\":\"blade-and-rose.png\",\"size\":28119,\"w\":512,\"h\":512,\"mime\":\"image\\/png\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"blade-and-rose-thumb@2x.png\",\"size\":34637,\"mime\":\"\",\"assetID\":\"79\"},\"w361h361c0\":{\"w\":\"361\",\"h\":\"361\",\"target_w\":\"361\",\"target_h\":\"361\",\"crop\":false,\"density\":\"1\",\"path\":\"blade-and-rose-w361h361.png\",\"size\":50580,\"mime\":\"\",\"assetID\":\"80\"},\"w512h512c0\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":false,\"density\":\"1\",\"path\":\"blade-and-rose-w512h512.png\",\"size\":28119,\"mime\":\"\",\"assetID\":\"94\"}}},\"alt_text\":\"Blade & Rose logo\",\"status\":\"1\"}', brandTitle='Blade & Rose', brandUpdated='2016-11-22 09:05:36' WHERE brandID='2'
0.3469  0.0024  No ids to log.
0.3469  0.0001  DELETE FROM perch2_shop_index WHERE itemKey='brandID' AND itemID='2'
0.366   0.0191  INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('brandID',2,'title','Blade & Rose'),('brandID',2,'_title','Blade & Rose'),('brandID',2,'slug','blade-rose'),('brandID',2,'description','Blade & Rose is a leading British children’s clothing brand specialising in a trendsetting range of leggings and clothing accessories. Each piece of the collection is unique and has the trademark design on the bum. A major component of the developm'),('brandID',2,'meta_description',''),('brandID',2,'url',''),('brandID',2,'image','/admin/resources/brands/blade-and-rose.png'),('brandID',2,'alt_text','Blade & Rose logo'),('brandID',2,'status','1'),('brandID',2,'brandID','2'),('brandID',2,'brandTitle','Blade & Rose'),('brandID',2,'brandCreated','2016-09-23 09:38:01'),('brandID',2,'brandUpdated','2016-11-22 09:05:36'),('brandID',2,'brandDeleted',''),('brandID',2,'itemID',''),('brandID',2,'itemRowID',''),('brandID',2,'_id',2)
0.4594  0.0934  [1] OPTIMIZE TABLE perch2_shop_index
15.0523 14.5929 Queries: 20
15.0524 0.0001  Memory: 5.3182

Product:

Time    Δ   Debug Message
0.0138  0   [1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='ca52d57154d4b896ab1457fac0e3bf88' LIMIT 1
0.0144  0.0006  UPDATE perch2_users SET userHash='6d95e6ed75fcf71cdcff443fb68ac0da' WHERE userID='1'
0.0146  0.0002  [74] SELECT p.privKey FROM perch2_user_privileges p
0.0161  0.0015  [37] SELECT * FROM (SELECT DISTINCT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID DESC) AS settings GROUP BY settingID, settingValue, userID
0.022   0.0059  Adding: Arithmetic
0.0379  0.0159  Adding: Breadcrumbs
0.0387  0.0008  [nil] SELECT collectionID, collectionKey, collectionEditRoles FROM perch2_collections WHERE collectionInAppMenu=1 ORDER BY collectionOrder ASC
0.0436  0.0049  [1] SELECT * FROM perch2_shop_products WHERE productID='11' AND productDeleted IS NULL LIMIT 1
0.0478  0.0041  Using template: /templates/shop/products/product.html
0.0622  0.0145  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
0.0631  0.0008  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.0642  0.0012  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.0645  0.0003  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
0.0716  0.0071  [1] SELECT * FROM perch2_resources WHERE resourceID='124' LIMIT 1
0.0826  0.011   [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceKey='thumb'
0.0831  0.0005  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=200 AND resourceTargetHeight=200 AND resourceCrop=1 AND resourceDensity=1
0.0835  0.0004  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=512 AND resourceTargetHeight=512 AND resourceCrop=1 AND resourceDensity=1
0.0839  0.0004  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=58 AND resourceTargetHeight=58 AND resourceCrop=1 AND resourceDensity=1
0.0852  0.0013  [1] SELECT * FROM perch2_resources WHERE resourceID='124' LIMIT 1
0.0855  0.0003  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceKey='thumb'
0.0858  0.0004  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=415 AND resourceTargetHeight=415 AND resourceCrop=1 AND resourceDensity=1
0.0862  0.0003  [1] SELECT * FROM perch2_resources WHERE resourceAWOL=0 AND resourceParentID='124' AND resourceTargetWidth=1024 AND resourceTargetHeight=1024 AND resourceCrop=0 AND resourceDensity=1
0.0876  0.0014  [1] SELECT * FROM perch2_categories WHERE catID=7 LIMIT 1
0.0879  0.0003  [1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
0.0881  0.0002  [1] SELECT * FROM perch2_categories WHERE catID=57 LIMIT 1
0.0883  0.0002  [1] SELECT * FROM perch2_categories WHERE catID=21 LIMIT 1
0.089   0.0007  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.0896  0.0006  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.09    0.0003  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
0.0908  0.0008  Logging resources for perch_shop
0.0908  0.0001  DELETE FROM perch2_resource_log WHERE appID='perch_shop' AND itemFK='productID' AND itemRowID=11
0.0982  0.0073  INSERT IGNORE INTO perch2_resource_log(`appID`, `itemFK`, `itemRowID`, `resourceID`) VALUES('perch_shop','productID',11,124),('perch_shop','productID',11,125),('perch_shop','productID',11,126),('perch_shop','productID',11,127),('perch_shop','productID',11,128),('perch_shop','productID',11,124),('perch_shop','productID',11,125),('perch_shop','productID',11,129),('perch_shop','productID',11,130)
0.099   0.0008  [4] SELECT * FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL
0.0994  0.0004  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:06:19' WHERE productID='12'
0.1004  0.0009  No ids to log.
0.1005  0.0001  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:06:19' WHERE productID='13'
0.101   0.0006  No ids to log.
0.1011  0.0001  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:06:19' WHERE productID='14'
0.1015  0.0004  No ids to log.
0.1016  0.0001  UPDATE perch2_shop_products SET productDynamicFields='{\"stock_location\":\"0\",\"status\":false}', productStockOnParent='0', productStatus='', productUpdated='2016-11-22 09:06:19' WHERE productID='15'
0.1022  0.0006  No ids to log.
0.1025  0.0003  UPDATE perch2_shop_products SET title='Ballerina Raincoat', sku='PC0003', stock_level=NULL, productDynamicFields='{\"_title\":\"Ballerina Raincoat\",\"slug\":\"ballerina-raincoat-pc0003\",\"description\":{\"_flang\":\"html\",\"raw\":\"<p>Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.<\\/p>\",\"processed\":\"<p>Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.<\\/p>\"},\"meta_description\":{\"_flang\":\"plain\",\"raw\":\"Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.\",\"processed\":\"Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.\"},\"favourite\":null,\"product_image_thumb\":{\"assetID\":\"124\",\"title\":\"Ballerina Raincoat\",\"_default\":\"\\/admin\\/resources\\/shop\\/ballerina-raincoat.jpg\",\"bucket\":\"shop\",\"path\":\"ballerina-raincoat.jpg\",\"size\":455835,\"w\":1000,\"h\":1000,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"ballerina-raincoat-thumb@2x.jpg\",\"size\":15018,\"mime\":\"\",\"assetID\":\"125\"},\"w200h200c1\":{\"w\":\"200\",\"h\":\"200\",\"target_w\":\"200\",\"target_h\":\"200\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w200h200.jpg\",\"size\":7916,\"mime\":\"\",\"assetID\":\"126\"},\"w512h512c1\":{\"w\":\"512\",\"h\":\"512\",\"target_w\":\"512\",\"target_h\":\"512\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w512h512.jpg\",\"size\":34636,\"mime\":\"\",\"assetID\":\"127\"},\"w58h58c1\":{\"w\":\"58\",\"h\":\"58\",\"target_w\":\"58\",\"target_h\":\"58\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w58h58.jpg\",\"size\":1628,\"mime\":\"\",\"assetID\":\"128\"}}},\"product_image_thumb_alt\":null,\"product_images\":[{\"product_image_main\":{\"assetID\":\"124\",\"title\":\"Ballerina Raincoat\",\"_default\":\"\\/admin\\/resources\\/shop\\/ballerina-raincoat.jpg\",\"bucket\":\"shop\",\"path\":\"ballerina-raincoat.jpg\",\"size\":455835,\"w\":1000,\"h\":1000,\"mime\":\"image\\/jpeg\",\"sizes\":{\"thumb\":{\"w\":\"150\",\"h\":\"150\",\"target_w\":150,\"target_h\":150,\"density\":2,\"path\":\"ballerina-raincoat-thumb@2x.jpg\",\"size\":15018,\"mime\":\"\",\"assetID\":\"125\"},\"w415h415c1\":{\"w\":\"415\",\"h\":\"415\",\"target_w\":\"415\",\"target_h\":\"415\",\"crop\":\"true\",\"density\":\"1\",\"path\":\"ballerina-raincoat-w415h415.jpg\",\"size\":25694,\"mime\":\"\",\"assetID\":\"129\"},\"w1024h1024c0\":{\"w\":\"1000\",\"h\":\"1000\",\"target_w\":\"1024\",\"target_h\":\"1024\",\"crop\":false,\"density\":\"1\",\"path\":\"ballerina-raincoat-w1024h1024.jpg\",\"size\":455835,\"mime\":\"\",\"assetID\":\"130\"}}},\"product_image_caption\":null}],\"brand\":\"6\",\"range\":[\"7\"],\"age_size\":[\"56\",\"57\"],\"category\":[\"21\"],\"special\":null,\"price\":{\"47\":\"24.99\",\"_default\":\"24.99\"},\"sale_price\":{\"47\":\"\",\"_default\":\"\"},\"trade_price\":{\"47\":\"\",\"_default\":\"\"},\"on_sale\":null,\"tax_group\":\"1\",\"stock_status\":\"0\",\"stock_location\":\"0\",\"max_in_cart\":null,\"requires_shipping\":\"1\",\"weight\":null,\"width\":null,\"height\":null,\"depth\":null,\"catalog_only\":\"0\",\"status\":\"1\"}', productStockOnParent='0', productSlug='ballerina-raincoat-pc0003', productStatus='1', productUpdated='2016-11-22 09:06:19' WHERE productID='11'
0.1059  0.0034  No ids to log.
0.106   0.0001  DELETE FROM perch2_shop_index WHERE itemKey='productID' AND itemID='11'
0.1129  0.0069  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
0.1137  0.0008  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
0.1141  0.0004  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
0.1145  0.0003  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
0.1165  0.002   [1] SELECT * FROM perch2_shop_brands WHERE brandID='6' AND brandDeleted IS NULL LIMIT 1
0.1173  0.0008  [1] SELECT * FROM perch2_categories WHERE catID=7 LIMIT 1
0.1176  0.0003  [1] SELECT * FROM perch2_categories WHERE catID=56 LIMIT 1
0.1178  0.0002  [1] SELECT * FROM perch2_categories WHERE catID=57 LIMIT 1
0.1182  0.0003  [1] SELECT * FROM perch2_categories WHERE catID=21 LIMIT 1
0.1185  0.0003  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.1188  0.0002  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.1191  0.0003  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.1193  0.0002  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.1195  0.0002  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=47 LIMIT 1
0.1197  0.0002  [1] SELECT * FROM perch2_shop_currencies WHERE currencyID=0 LIMIT 1
0.1203  0.0005  [1] SELECT * FROM perch2_shop_tax_groups WHERE groupID='1' AND groupDeleted IS NULL LIMIT 1
0.1221  0.0018  INSERT INTO perch2_shop_index (itemKey, itemID, indexKey, indexValue) VALUES ('productID',11,'_title','Ballerina Raincoat'),('productID',11,'slug','ballerina-raincoat-pc0003'),('productID',11,'description','Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.'),('productID',11,'meta_description','Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps.'),('productID',11,'favourite',''),('productID',11,'product_image_thumb','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_image_thumb_alt',''),('productID',11,'product_images.product_image_main','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_images.product_image_main','/admin/resources/shop/ballerina-raincoat.jpg'),('productID',11,'product_images.product_image_caption',''),('productID',11,'brand','6'),('productID',11,'brand.title','Powell Craft'),('productID',11,'brand._title','Powell Craft'),('productID',11,'brand.slug','powell-craft'),('productID',11,'brand.url',''),('productID',11,'brand.alt_text','Powell craft'),('productID',11,'brand.status','1'),('productID',11,'brand.brandID','6'),('productID',11,'brand.brandTitle','Powell Craft'),('productID',11,'brand.brandCreated','2016-09-23 09:39:14'),('productID',11,'brand.brandUpdated','2016-11-21 16:12:41'),('productID',11,'brand.brandDeleted',''),('productID',11,'_category','ranges/girls/'),('productID',11,'_category','age-size/1-2-years/'),('productID',11,'_category','age-size/3-4-years/'),('productID',11,'_category','products/rain-coats/'),('productID',11,'price','24.99'),('productID',11,'price.gbp','24.99'),('productID',11,'sale_price',''),('productID',11,'sale_price.gbp',''),('productID',11,'trade_price',''),('productID',11,'trade_price.gbp',''),('productID',11,'on_sale',''),('productID',11,'tax_group','1'),('productID',11,'tax_group.groupID','1'),('productID',11,'tax_group.groupTitle','Default'),('productID',11,'tax_group.groupSlug','default'),('productID',11,'tax_group.groupTaxRate','buyer'),('productID',11,'tax_group.groupCreated','2016-09-23 09:34:09'),('productID',11,'tax_group.groupUpdated',''),('productID',11,'tax_group.groupDeleted',''),('productID',11,'stock_status','0'),('productID',11,'stock_location','0'),('productID',11,'max_in_cart',''),('productID',11,'requires_shipping','1'),('productID',11,'weight',''),('productID',11,'width',''),('productID',11,'height',''),('productID',11,'depth',''),('productID',11,'catalog_only','0'),('productID',11,'status','1'),('productID',11,'productID','11'),('productID',11,'title','Ballerina Raincoat'),('productID',11,'sku','PC0003'),('productID',11,'productSlug','ballerina-raincoat-pc0003'),('productID',11,'stock_level',''),('productID',11,'parentID',''),('productID',11,'productVariantDesc',''),('productID',11,'productOrder','1'),('productID',11,'productHasVariants','1'),('productID',11,'productStockOnParent','0'),('productID',11,'productTemplate','product.html'),('productID',11,'productCreated','2016-10-07 12:24:48'),('productID',11,'productUpdated','2016-11-22 09:06:19'),('productID',11,'productDeleted',''),('productID',11,'productStatus','1'),('productID',11,'itemID',''),('productID',11,'itemRowID',''),('productID',11,'has_variants','1'),('productID',11,'_variant_opts','1-2 Years|12,2-3 Years|13,4/5 Years|14,6/7 Years|15'),('productID',11,'_id',11)
0.2617  0.1396  [1] OPTIMIZE TABLE perch2_shop_index
17.0382 16.7765 DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'
17.5658 0.5276  INSERT INTO perch2_shop_search(itemKey,itemType,searchBody) VALUES(11,'product',' PC0003 Ballerina Raincoat ballerina raincoat pc0003 Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps. Pu hooded rain mac in ballerina print. The coat is lined in soft towelling, has popper fastenings and two pockets with flaps. 6 Girls 1-2 Years, 3-4 Years Rain Coats 1 0 0 1 0 1 ')
17.5666 0.0008  [4] SELECT productID, productVariantDesc, stock_level FROM perch2_shop_products WHERE parentID=11 AND productDeleted IS NULL ORDER BY productOrder ASC
17.5672 0.0006  [1] SELECT o.* FROM perch2_shop_options o, perch2_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=11 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
17.5675 0.0003  [19] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch2_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
17.5679 0.0004  [4] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch2_shop_product_option_values pov, perch2_shop_options o, perch2_shop_option_values ov WHERE pov.productID='11' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
17.5751 0.0072  [6] SELECT * FROM perch2_shop_brands WHERE 1=1 AND brandDeleted IS NULL ORDER BY brandTitle ASC
17.5959 0.0208  [4] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='ranges' ORDER BY catTreePosition ASC
17.5969 0.001   [6] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='age-size' ORDER BY catTreePosition ASC
17.5978 0.0009  [17] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='products' ORDER BY catTreePosition ASC
17.5993 0.0015  [2] SELECT * FROM perch2_categories c, perch2_category_sets s WHERE c.setID=s.setID AND s.setSlug='specials' ORDER BY catTreePosition ASC
17.6001 0.0008  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
17.6013 0.0012  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
17.6023 0.0011  [1] SELECT * FROM perch2_shop_currencies WHERE currencyActive=1 ORDER BY currencyID=47 DESC, currencyCode
17.6034 0.0011  [1] SELECT * FROM perch2_shop_tax_groups WHERE 1=1 AND groupDeleted IS NULL ORDER BY groupTitle ASC
17.6091 0.0056  Queries: 77
17.6091 0   Memory: 6.1078

The same query again is taking 16 seconds to complete: DELETE FROM perch2_shop_search WHERE itemKey=11 AND itemType='product'


Edit: Another thing, testing in sequel pro the OPTIMIZE TABLE perch2_shop_index takes around 3 seconds and outputs this:

puds_perch.perch2_shop_index    optimize    note    Table does not support optimize, doing recreate + analyze instead
puds_perch.perch2_shop_index    optimize    status  OK

Edit 2: Yes, messing about and changing the table type of perch_shop_index to MyISAM to match perch_content_index completely removes any locking and saving is now instant

Drew McLellan

Drew McLellan 2638 points
Perch Support

It should be fine as MyISAM, so you can stick with that.

Ok, thanks Drew that seems to have sorted out the problems on the production server too. I have also spotted a minor bug where updating an order for a deleted product throws a hard error with a white page:

[22-Nov-2016 10:33:44 UTC] PHP Fatal error:  Call to a member function to_array() on boolean in /xxx/xxx/public_html/admin/addons/apps/perch_shop/lib/PerchShop_Order.class.php on line 525

I can expand this into a new thread if that is preferred?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, please.