Download query optimization on Zed

ilyafil2012

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;    

}

Alberto Reyer

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 connection

Thats probably not a default timeout for the connection but the PHP process time max_execution_time ( https://www.php.net/manual/en/info.configuration.php#ini.max-execution-time ) you are hitting.
You can increase this in the action with set_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.

ilyafil2012

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)

Oleksandr Peresypkin

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 index fk_sales_order, fk_oms_order_item_state

Table spy_sales_order_totalscomposite covering index fk_sales_order, created_at, id_sales_order_totals, grand_total.

If you provided the final query, it would be helpful.