2. PHPRunner General questions
  3. [SOLVED]  Select Query Optimization
This topic is locked
[SOLVED]

 Select Query Optimization

 		7/31/2012 9:53:06 PM
PHPRunner General questions
S
 Stucco author

Hi, I have a situation where I have a slow performing query that I cannot optimize any more with indexes due to nested subqueries. This is the generated query.



SELECT id, date, shipment_id, (

SELECT count(*) FROM (

SELECT shipment_id FROM logs

) subQuery_cnt

WHERE subQuery_cnt.shipment_id=shipments.id

) as logs_cnt

FROM shipments

WHERE customer_id=231

ORDER BY shipment_id ASC;


I am hoping that there is some way to adjust the generated query to this simple difference



SELECT id, DATE, shipment_id, (

SELECT COUNT( shipment_id )

FROM LOGS

WHERE shipment_id = shipments.id

) AS logs_cnt

FROM shipments

WHERE customer_id =231

ORDER BY shipment_id ASC;


The explain plan for the current query is



id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY shipments ref forecast_shipment_idx,customer_id customer_id 5 const 89 Using where; Using filesort

2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 915515 Using where

3 DERIVED logs index NULL SHIPMENT_ID_IDX 5 NULL 915515 Using index


I can't optimize this anymore with indexes due to the nested subqueries.
The explain plan for the second one is



id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY shipments ref forecast_shipment_idx,customer_id customer_id 5 const 89 Using where; Using filesort

2 DEPENDENT SUBQUERY LOGS ref LOGS_SHIPMENT_ID_TIME,SHIPMENT_ID_IDX SHIPMENT_ID_IDX 5 shippingtracker.shipments.id 37 Using where; Using index


Is there anything that I can do to improve the performance?
Thanks!!
S
 		Stucco author 8/8/2012

Hi,
I am hoping someone knows a way to adjust the queries.
Thanks!!
Sergey Kornilov admin 8/9/2012

I would suggest to ask this question in general SQL forum like stackoverflow.com
S
 		Stucco author 8/13/2012



I would suggest to ask this question in general SQL forum like stackoverflow.com


Hi. My problem is not how to optimize the SQL. I have already done this. My question is how to get PHPRunner to use my new SQL. The query that PHPRunner is generating to join parent and children is not efficient.
Thank you!!
S
 		Stucco author 8/14/2012

I found this when searching the forums that looks like it may work for me.
Perfomance Issue with Master - Child



As workaround you can switch off subqueries in the \include\appsettings.php:
$bSubqueriesSupported=false;


How would I permanently set this so I do not need to remember for each time I build?
Also, I am wondering if there is a way to modify the method of generating SQL to retrieve master/child record counts in general, besides this setting.
Thank you!
Sergey Kornilov admin 8/14/2012

You can add the following to AfterAppInit event to make it permanent:

$bSubqueriesSupported=false;


There is no other way to modify the general master-details behavior.