# 250ms user time, 0 system time, 36.73M rss, 103.10M vsz
# Current date: Wed Jul 3 17:41:19 2019
# Hostname: webserver.outdoorshop.ch
# Files: /var/log/mysql/slow-query.log
# Overall: 504 total, 60 unique, 0.74 QPS, 0.03x concurrency _____________
# Time range: 2019-07-03 17:30:01 to 17:41:18
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 23s 22us 840ms 45ms 501ms 134ms 5ms
# Lock time 133ms 10us 4ms 263us 725us 314us 236us
# Rows sent 2.20k 0 183 4.47 6.98 12.54 2.90
# Rows examine 2.05M 0 124.75k 4.17k 28.66k 18.39k 17.65
# Rows affecte 0 0 0 0 0 0 0
# Query size 596.00k 27 2.70k 1.18k 1.96k 896.08 1.96k
# Profile
# Rank Query ID Response time Calls R/Call V/M It
# ==== =============================== ============= ===== ====== ===== ==
# 1 0x7AB6CD4E0F6CCB586F91BB5A56... 19.4274 85.7% 259 0.0750 0.43 SELECT shp_group_disco shp_group_disco_items shp_group_disco shp_group_disco_items shp_defined_sizes shp_items_v shp_sizes
# 2 0xEA1649F6B3201A46471DA37853... 0.5639 2.5% 4 0.1410 0.01 SELECT shp_users shp_useraddress_shipping shp_useraddress_billing
# 3 0xAFCCC404B5B6A0693D8201D4C3... 0.5043 2.2% 4 0.1261 0.11 SELECT shp_basket invoices shp_users shp_useraddress_billing shp_useraddress_shipping
# 4 0xDC67BD6F8CEF8E6FB21F92D2D0... 0.4650 2.1% 5 0.0930 0.00 SELECT shp_items_titles shp_items_v
# 5 0x618372368997B3BC35107C5922... 0.2731 1.2% 2 0.1366 0.00 SELECT invoices shp_basket invoice_address shp_users
# 6 0x9059D84BF45A2A1BF3740FABDA... 0.2100 0.9% 2 0.1050 0.00 SELECT shp_items shp_defined_sizes shp_sizes
# 7 0xAFC825D91979D2760D83C94CCE... 0.1905 0.8% 43 0.0044 0.00 SELECT shp_stock shp_quantity
# MISC 0xMISC 1.0299 4.5% 185 0.0056 0.0 <53 ITEMS>
# Query 1: 0.43 QPS, 0.03x concurrency, ID 0x7AB6CD4E0F6CCB586F91BB5A5636E8C9 at byte 59920
# Scores: V/M = 0.43
# Time range: 2019-07-03 17:31:11 to 17:41:18
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 51 259
# Exec time 85 19s 2ms 840ms 75ms 526ms 179ms 8ms
# Lock time 60 80ms 233us 2ms 309us 690us 176us 260us
# Rows sent 42 966 1 7 3.73 4.96 1.56 3.89
# Rows examine 0 7.61k 3 81 30.07 76.28 22.52 17.65
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 88 528.37k 2.04k 2.04k 2.04k 1.96k 0 1.96k
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms #######################
# 100ms ############
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_group_disco'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_group_disco`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_group_disco_items'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_group_disco_items`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_defined_sizes'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_defined_sizes`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_items_v'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_items_v`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_sizes'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_sizes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT i.id,p.id as size_id,p.code,p.title,s.price,s.price_eur
,fgetStockAll(i.id,p.id) real_quantity
,IF(i.always_available='y',1,IFNULL(s.available,0)+fgetStockAll(i.id,p.id)) quantity
, i.item_discount*IF(i.discount='y',1,0) item_discount
, IF(i.always_available='y' or IFNULL(s.available,0), 1, 0) supplier_availability
, fgetStockAll(i.id,p.id) pure_stock
, IFNULL(s.available_date,'') available_date
, IFNULL(
(select max(total_amount)
from shp_group_disco gd
join shp_group_disco_items gdi on gd.id=gdi.group_disco_id
where (gdi.item_id = i.id
or gdi.parent_id=i.parent_id
or gdi.parent_id=i.pparent_id
or gdi.parent_id=i.ppparent_id
or gdi.parent_id=i.pppparent_id
or gdi.parent_id=i.ppppparent_id
or gdi.parent_id=i.pppppparent_id)
and exists (select null from shp_group_disco_items gdi1 where gdi1.group_disco_id=gd.id
and gdi1.brand_id in (i.item_brand_id,-1)))
,0) group_disco
, IFNULL(
(select allow_promo
from shp_group_disco gd
join shp_group_disco_items gdi on gd.id=gdi.group_disco_id
where (gdi.item_id = i.id
or gdi.parent_id=i.parent_id
or gdi.parent_id=i.pparent_id
or gdi.parent_id=i.ppparent_id
or gdi.parent_id=i.pppparent_id
or gdi.parent_id=i.ppppparent_id
or gdi.parent_id=i.pppppparent_id)
and exists (select null from shp_group_disco_items gdi1 where gdi1.group_disco_id=gd.id
and gdi1.brand_id in (i.item_brand_id,-1))
order by total_amount desc limit 1
)
,0) allow_promo
FROM shp_defined_sizes p
JOIN shp_items_v i on p.type_id=i.size_type_id
LEFT JOIN shp_sizes s ON i.id=s.id AND s.size_id=p.id
WHERE i.id='2054878' AND (s.hidden='n'
OR (IFNULL(s.hidden,'')='' and p.hidden=0) or fgetStockAll(i.id,p.id)>0) ORDER BY p.sort\G
# Query 2: 0.01 QPS, 0.00x concurrency, ID 0xEA1649F6B3201A46471DA37853067813 at byte 10612
# Scores: V/M = 0.01
# Time range: 2019-07-03 17:30:38 to 17:35:44
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 4
# Exec time 2 564ms 96ms 197ms 141ms 189ms 40ms 176ms
# Lock time 0 328us 76us 86us 82us 84us 4us 84us
# Rows sent 0 4 1 1 1 1 0 1
# Rows examine 19 414.42k 82.47k 124.74k 103.61k 123.85k 21.14k 123.85k
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 0 2.62k 665 677 669.50 652.75 0 652.75
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_users'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_users`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_useraddress_shipping'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_useraddress_shipping`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_useraddress_billing'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_useraddress_billing`\G
# EXPLAIN /*!50100 PARTITIONS*/
select count(*) as totalRecord
from shp_users u
left join shp_useraddress_shipping s on u.id=s.id_user
left join shp_useraddress_billing b on u.id=b.id_user
where 1 and (u.id = 0 or 0=0)
and (IFNULL(u.email,'') like '%')
and ((u.hidden=0 and ''='0') or (u.hidden=1 and ''='1') or (''=''))
and (IFNULL(u.firm,'') like '%' or IFNULL(s.firm,'') like '%')
and (IFNULL(u.first_name,'') like '%' or IFNULL(s.first_name,'') like '%')
and (IFNULL(u.last_name,'') like '%' or IFNULL(s.last_name,'') like '%')
and (IFNULL(s.city,'') like '%' or IFNULL(b.city,'') like '%')
and (IFNULL(s.postal,'') like '%' or IFNULL(b.postal,'') like '%')\G
# Query 3: 0.01 QPS, 0.00x concurrency, ID 0xAFCCC404B5B6A0693D8201D4C3EF295C at byte 58695
# Scores: V/M = 0.11
# Time range: 2019-07-03 17:30:38 to 17:35:44
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 4
# Exec time 2 504ms 906us 264ms 126ms 253ms 120ms 241ms
# Lock time 1 1ms 129us 669us 337us 657us 208us 503us
# Rows sent 1 43 1 20 10.75 19.46 9.00 19.46
# Rows examine 11 249.67k 90 124.75k 62.42k 123.85k 61.88k 123.85k
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 0 3.73k 950 962 954.50 918.49 0 918.49
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us ################################
# 1ms ################################
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_basket'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_basket`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'invoices'\G
# SHOW CREATE TABLE `outdoorshop`.`invoices`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_users'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_users`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_useraddress_billing'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_useraddress_billing`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_useraddress_shipping'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_useraddress_shipping`\G
# EXPLAIN /*!50100 PARTITIONS*/
select u.*,concat(u.first_name, ' ',u.last_name) as full_name
, b.address,b.city,b.country,b.region,b.postal
, (select count(id) as orders from shp_basket where id_user=u.id) as order_cnt
, (select count(id) as orders from invoices where id_user=u.id) as invoice_cnt
from shp_users u
left join shp_useraddress_billing b on u.id=b.id_user
left join shp_useraddress_shipping s on u.id=s.id_user
where 1 and (u.id = 0 or 0=0)
and (IFNULL(u.email,'') like '%')
and ((u.hidden=0 and ''='0') or (u.hidden=1 and ''='1') or (''=''))
and (IFNULL(u.firm,'') like '%' or IFNULL(s.firm,'') like '%')
and (IFNULL(u.first_name,'') like '%' or IFNULL(s.first_name,'') like '%')
and (IFNULL(u.last_name,'') like 'balogh%' or IFNULL(s.last_name,'') like 'balogh%')
and (IFNULL(s.city,'') like '%' or IFNULL(b.city,'') like '%')
and (IFNULL(s.postal,'') like '%' or IFNULL(b.postal,'') like '%')
order by u.id desc limit 0,20\G
# Query 4: 0.01 QPS, 0.00x concurrency, ID 0xDC67BD6F8CEF8E6FB21F92D2D0FA36CE at byte 579077
# Scores: V/M = 0.00
# Time range: 2019-07-03 17:31:11 to 17:40:32
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 5
# Exec time 2 465ms 76ms 109ms 93ms 105ms 11ms 95ms
# Lock time 1 2ms 229us 690us 481us 657us 195us 596us
# Rows sent 0 5 1 1 1 1 0 1
# Rows examine 19 407.51k 81.50k 81.50k 81.50k 81.50k 0 81.50k
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 0 1015 190 220 203 212.52 13.11 192.76
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ##########################################
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_items_titles'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_items_titles`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_items_v'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_items_v`\G
# EXPLAIN /*!50100 PARTITIONS*/
select it.item_id from shp_items_titles it
join shp_items_v i on i.id=it.item_id
where REPLACE(REPLACE(it.title,'/',' '),'+',' ')='Arc\'teryx FERNIE SS SHIRT WOMEN\'S (purple reign)'
and shops & 1\G
# Query 5: 0.01 QPS, 0.00x concurrency, ID 0x618372368997B3BC35107C5922F1DB0C at byte 6383
# Scores: V/M = 0.00
# Time range: 2019-07-03 17:30:12 to 17:35:59
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2
# Exec time 1 273ms 136ms 137ms 137ms 137ms 1ms 137ms
# Lock time 0 281us 118us 163us 140us 163us 31us 140us
# Rows sent 2 60 30 30 30 30 0 30
# Rows examine 11 234.70k 117.35k 117.35k 117.35k 117.35k 0 117.35k
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 0 2.64k 1.32k 1.32k 1.32k 1.32k 0 1.32k
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'invoices'\G
# SHOW CREATE TABLE `outdoorshop`.`invoices`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_basket'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_basket`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'invoice_address'\G
# SHOW CREATE TABLE `outdoorshop`.`invoice_address`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_users'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT i.*,IF(concat(fn.field_value,',',ln.field_value)=','
, firmn.field_value
,concat(fn.field_value,',',ln.field_value)) as customer_name
, IFNULL(shp_basket.gmt_completed,0) gmt_completed, shp_basket.reservationReference, shp_basket.gmt_CardNumber
, DATE_FORMAT(i.date_order,'%d.%m.%Y') datum
FROM invoices i
left join shp_basket on shp_basket.id=i.shp_basket_id
LEFT JOIN invoice_address firmn ON i.id=firmn.invoice_id and firmn.field_name='firm_billing'
LEFT JOIN invoice_address fn ON i.id=fn.invoice_id and fn.field_name='vorname_billing'
LEFT JOIN invoice_address ln ON i.id=ln.invoice_id and ln.field_name='name_billing'
LEFT JOIN invoice_address fns ON i.id=fns.invoice_id and fns.field_name='vorname'
LEFT JOIN invoice_address lns ON i.id=lns.invoice_id and lns.field_name='name'
LEFT JOIN invoice_address plz ON i.id=plz.invoice_id and plz.field_name='plz_billing'
LEFT JOIN invoice_address plzs ON i.id=plzs.invoice_id and plzs.field_name='plz'
LEFT JOIN invoice_address city ON i.id=city.invoice_id and city.field_name='city_billing'
LEFT JOIN invoice_address citys ON i.id=citys.invoice_id and citys.field_name='city'
LEFT JOIN shp_users su ON i.id_user=su.id
where 1
and IFNULL(i.user_id,0) <> 5 and stock_id in (1,2,3) ORDER BY inv_number DESC limit 0,30\G
# Query 6: 0.08 QPS, 0.01x concurrency, ID 0x9059D84BF45A2A1BF3740FABDA103070 at byte 717613
# Scores: V/M = 0.00
# Time range: 2019-07-03 17:40:48 to 17:41:14
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2
# Exec time 0 210ms 105ms 105ms 105ms 105ms 90us 105ms
# Lock time 1 2ms 868us 878us 873us 878us 7us 873us
# Rows sent 0 2 1 1 1 1 0 1
# Rows examine 0 4 2 2 2 2 0 2
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 0 1.79k 914 914 914 914 0 914
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_items'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_items`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_defined_sizes'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_defined_sizes`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_sizes'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_sizes`\G
# EXPLAIN /*!50100 PARTITIONS*/
select
IF(i.size_type_id=2,
IF((fgetStockAll(i.id, ds.id) or s.available or (i.always_available='y' and s.hidden='n'))
, '
verfügbar
'
, '
derzeit ausverkauft
'
)
, CONCAT('
verfügbare Grössen: ',GROUP_CONCAT(IFNULL(
IF((fgetStockAll(i.id, ds.id) or s.available or (i.always_available='y' and s.hidden='n')),ds.title,'')
,'') order by ds.sort separator ' '),'
')
) stocks
from shp_items i
join shp_defined_sizes ds on i.size_type_id=ds.type_id and
(ds.item_id=i.id or i.size_type_id>1)
left join shp_sizes s on i.id=s.id and s.size_id=ds.id
where 1
and i.id=2000563
#and (fgetStockAll(i.id, ds.id) or s.available or (i.always_available='y' and s.hidden='n'))
order by ds.sort\G
# Query 7: 0.09 QPS, 0.00x concurrency, ID 0xAFC825D91979D2760D83C94CCE3ED70D at byte 663118
# Scores: V/M = 0.00
# Time range: 2019-07-03 17:33:12 to 17:41:18
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 43
# Exec time 0 190ms 2ms 13ms 4ms 9ms 2ms 4ms
# Lock time 8 11ms 56us 4ms 261us 490us 579us 73us
# Rows sent 5 125 1 3 2.91 2.90 0.40 2.90
# Rows examine 0 614 5 20 14.28 17.65 3.60 13.83
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 2 15.55k 355 371 370.26 363.48 3.64 363.48
# String:
# Databases outdoorshop
# Hosts localhost
# Users outdooruser
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms ###
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_stock'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_stock`\G
# SHOW TABLE STATUS FROM `outdoorshop` LIKE 'shp_quantity'\G
# SHOW CREATE TABLE `outdoorshop`.`shp_quantity`\G
# EXPLAIN /*!50100 PARTITIONS*/
select shp_stock.name stock_name
, fgetLastPrice(2064209,14188,shp_stock.id) lastprice
, fgetAvgPrice(2064209,14188,shp_stock.id) avgprice
, stock_border, shp_stock.id stock_id
from shp_stock
left join (select * from shp_quantity where id=2064209 and size_code=14188) shp_quantity
on shp_stock.id=stock_id
where shp_stock.id in (1,2,3)\G