how to optimize this code in php file

Question

Recently our server provider told us there are many tmp tables created on disk by Mysql , and cause Disk I/O abuse. I think this happens because of the below code:

$features = db_get_array("SELECT DISTINCT(a.description), b.parent_id FROM ?:product_features_descriptions as a LEFT JOIN ?:product_features as b ON a.feature_id = b.feature_id WHERE a.lang_code = ?s AND b.feature_type != ?s ORDER BY b.parent_id, a.description ASC", DESCR_SL, 'G');

How can I optimize this, could anyone help me ?

Answer 1

Not sure what your table structure is between product_feature_descriptions and product features. It seems like you should be doing an inner join instead of a left join if product_features has a one to many with product_feature_descriptions.

At any rate, ditch the "distinct" and try a "group by".

That will improve performance.

SELECT a.description
,b.parent_id 
FROM ?:product_features_descriptions as a 
LEFT JOIN ?:product_features as b 
ON a.feature_id = b.feature_id 
WHERE a.lang_code = ?s 
AND   b.feature_type != ?s 
GROUP BY a.description, b.parent_id
ORDER BY b.parent_id, 
a.description ASC

Answer 2

for performance improvement also check indexes in your database for product_features_descriptions and product_features tables.

• have an index on product_features (feature_id, parent_id, feature_type) & on product_features_descriptions (feature_id,description,lang_code)

this should make optimizer access data from indexes and will improve performance