Download query optimization on Zed
I'm trying to modify the sales table on zed by adding a country name column to it. It seems to work relatively well on the main table itself, but trying to download a csv leads to a request of >60s, which seems to trigger the default client connection of 60s, thus dropping the connection. At least that's my hypothesis.
The download query command is as follows, the last 2 joins and the withColumn is what has been added and what has been causing us issues. Is there something i could improvide with the query, or do we need to use a different solution ?
protected function getDownloadQuery(): ModelCriteria
{
$salesOrderQuery = $this->queryBuilder->buildQuery();
$salesOrderQuery->orderBy(SpySalesOrderTableMap::COL_ID_SALES_ORDER, \Spryker\Zed\PropelOrm\Business\Runtime\ActiveQuery\Criteria::DESC);
$salesOrderQuery->addJoin(SpySalesOrderTableMap::COL_FK_SALES_ORDER_ADDRESS_SHIPPING, SpySalesOrderAddressTableMap::COL_ID_SALES_ORDER_ADDRESS, Criteria::LEFT_JOIN);
$salesOrderQuery->addJoin(SpySalesOrderAddressTableMap::COL_FK_COUNTRY, SpyCountryTableMap::COL_ID_COUNTRY, Criteria::LEFT_JOIN);
$salesOrderQuery->withColumn(SpyCountryTableMap::COL_NAME, static::SHIPPING_COUNTRY);
return $salesOrderQuery;
}
Answers
-
Enable your propel log locally, get the query from the log and run it with
EXPLAIN
in front of the query in your DB, this will give you a fairly lengthy output.
In this output I would look for full table scanes (hinting that an index is missing or not used during the join), as well as for big data sets.
In addition check that the query results is not becomming bigger then you expected due to the join. Sometimes it might happen that instead of just adding a new column to the result instead the result set becomes a multiple of your orders times addresses times countries.
Regarding:
> which seems to trigger the default client connection of 60s, thus dropping the connectionThats probably not a default timeout for the connection but the PHP process time
max_execution_time
( ) you are hitting.
You can increase this in the action withset_ini('max_execution_time', <number>)
Another option would be to add a limit to the query and sort by
modified_at
to download only the most recent orders.0 -
It does seem to do a different type of query for the three tables in question, but i'm not sure how much that impacts it. I do see that the other table columns are added via subqueries in the query handler interface, so i guess i have to do that instead.
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
PRIMARY
spy_sales_order
index
PRIMARY
4
1
1
PRIMARY
spy_sales_order_address
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.fk_sales_order_address_shipping
1
Using where
1
PRIMARY
spy_country
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order_address.fk_country
1
Using where
7
DEPENDENT SUBQUERY
discounts
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using index
7
DEPENDENT SUBQUERY
spy_sales_discount
ALL
index-spy_sales_discount-fk_sales_order
1
Using where; Using join buffer (flat, BNL join)
6
DEPENDENT SUBQUERY
pt
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using index
6
DEPENDENT SUBQUERY
spy_sales_payment
ALL
index-spy_sales_payment-fk_sales_order,index-spy_sales_payment-fk_sales_payment_method_type
1
Using where; Using join buffer (flat, BNL join)
6
DEPENDENT SUBQUERY
spy_sales_payment_method_type
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_payment.fk_sales_payment_method_type
1
5
DEPENDENT SUBQUERY
sccg
eq_ref
PRIMARY,spy_sales_order-customer_reference
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using where
5
DEPENDENT SUBQUERY
spy_customer
eq_ref
PRIMARY,spy_customer-customer_reference
spy_customer-customer_reference
767
eu-docker.sccg.customer_reference
1
Using index
5
DEPENDENT SUBQUERY
spy_customer_group_to_customer
ref
fk_customer_group-fk_customer,index-spy_customer_group_to_customer-fk_customer
index-spy_customer_group_to_customer-fk_customer
4
eu-docker.spy_customer.id_customer
1
5
DEPENDENT SUBQUERY
spy_customer_group
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_customer_group_to_customer.fk_customer_group
1
4
DEPENDENT SUBQUERY
spy_sales_order_item
ref
index-spy_sales_order_item-fk_sales_order
index-spy_sales_order_item-fk_sales_order
4
eu-docker.spy_sales_order.id_sales_order
1
Using index
4
DEPENDENT SUBQUERY
sso
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using where; Using index
3
DEPENDENT SUBQUERY
sso
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using index; Using temporary; Using filesort
3
DEPENDENT SUBQUERY
spy_sales_order_item
ALL
index-spy_sales_order_item-fk_sales_order,index-spy_sales_order_item-fk_oms_order_item_state
1
Using where; Using join buffer (flat, BNL join)
3
DEPENDENT SUBQUERY
spy_oms_order_item_state
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order_item.fk_oms_order_item_state
1
2
DEPENDENT SUBQUERY
sso
eq_ref
PRIMARY
PRIMARY
4
eu-docker.spy_sales_order.id_sales_order
1
Using index; Using temporary; Using filesort
2
DEPENDENT SUBQUERY
spy_sales_order_totals
ALL
index-spy_sales_order_totals-fk_sales_order
1
Using where; Using join buffer (flat, BNL join)
0 -
Checking a standard demo shop, I can see that adding the following indexes could help avoid some full table scans:
Table
spy_sales_order_item
composite indexfk_sales_order
,fk_oms_order_item_state
Table
spy_sales_order_totals
composite covering indexfk_sales_order
,created_at
,id_sales_order_totals
,grand_total
.If you provided the final query, it would be helpful.
1
Categories
- All Categories
- 42 Getting Started & Guidelines
- 7 Getting Started in the Community
- 8 Additional Resources
- 7 Community Ideas and Feedback
- 69 Spryker News
- 896 Developer Corner
- 758 Spryker Development
- 83 Spryker Dev Environment
- 361 Spryker Releases
- 3 Oryx frontend framework
- 34 Propel ORM
- 68 Community Projects
- 3 Community Ideation Board
- 30 Hackathon
- 3 PHP Bridge
- 6 Gacela Project
- 23 Job Opportunities
- 3.2K 📜 Slack Archives
- 116 Academy
- 5 Business Users
- 370 Docker
- 551 Slack General
- 2K Help
- 75 Knowledge Sharing
- 6 Random Stuff
- 4 Code Testing
- 32 Product & Business Questions
- 68 Spryker Safari Questions
- 50 Random