Help! Hostmonster shut up my site saying mySQL too slow!!!

[xiuzhe]

Hostmonster shut up my site saying mySQL too slow!!!

I got info. from them as below,

bad queries such as this: select p.products_id, p.products_type,p.products_status,p.products_price,p.products_price_retail,p.pro ducts_price_sample,p.product_is_wholesale,p.product_wholesale_min,p.products _quantity, p.products_quantity_order_min, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_de script ion, IF(s.status = 1, s.specia ls_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_c all, p.product_is_always_free_shipping, p.products_qty_box_status from products_de script ion pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id where p.products_status = 1 and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and ( p2c.categories_id =1 or p2c.categories_id =7 or p2c.categories_id =184 or p2c.categories_id =185 or p2c.categories_id =186 or p2c.categories_id =187 or p2c.categories_id = 188 or p2c.categories_id =189 or p2c.categories_id =190 or p2c.categories_id =191 or p2c.categories_id =192 or p2c.categories_id =156 or p2c.categories_id =193 or p2c.categories_i d =194 or p2c.categories_id =195 or p2c.categories_id =196 or p2c.categories_id =197 or p2c.categories_id =164 or p2c.categories_id =167 or p2c.categories_id =168 or p2c.categorie s_id =169 or p2c.categories_id =170 or p2c.categories_id =171 or p2c.categories_id =172 or p2c.categories_id =173 or p2c.categories_id =174 or p2c.categories_id =175 or p2c.categories_id =176 or p2c.categories_id =177 or p2c.categories_id =178 or p2c.categories_id =769 or p2c.categories_id =179 or p2c.categories_id =181 or p2c.categories_id =182 or p2c.categories_id =183 or p2c.categories_id =2 or p2c.categories_id =18 or p2c.categories_id =198 or p2c.categories_id =199 or p2c.categories_id =19 or p2c.categories_id =20 or p2c.categories_id =148 or p2c.categories_id =149 or p2c.categories_id =200 or p2c.categories_id =201 or p2c.categories_id =202 or p2c.categories_id =203 or p2c.categories_id =204 or p2c.categories_id =150 or p2c.categories_id =151 or p2c.categories_id =205 or p2c.categories_id =206 or p2c.categories_id =207 or p2c.categories_id =152 or p2c.categories_id =153 or p2c.categories_id =154 or p2c.categories_id =155 or p2c.categories_id =208 or p2c.categories_id =209 or p2c.categories_id =3 or p2c.categories_id =775 or p2c.categories_id =776 or p2c.categories_id =812 or p2c.categories_id =817 or p2c.categories_id =821 or p2c.categories_id =21 or p2c.categories_id =141 or p2c.categories_id =259 or p2c.

Full read-out in the mysql slow queries log in their ~/tmp directory.


Pls anyone could help what to do????

[DrByte]

What if you choose a specific category instead of telling it to search all sub-categories?

I doubt your database problems are related to the many similar "OR" statements in the query (which are caused by checking the box for searching all subcategories). Instead, it's almost certainly related to the extra fields you've added to the database and likely the search code, combined with probably non-existent indexes on the fields added into the search.

You'll need to work with whoever wrote the customizations you've added to make their code and queries more efficient.

[xiuzhe]

Could you pls let me know how to fix this issue specifically?

[kuroi]

This query needs to be rewritten. The section that starts

and ( p2c.categories_id =1 or p2c.categories_id =7 or p2c.categories_id =184 or p2c.categories_id =185 or p2c.categories_id =186 or p2c.categories_id =187 or ...

should be rewritten to formulate the query as using the following model

and p2c.categories_id in (1,7,184,185,186,187, ....);

If the list is in an array the php syntax when building the SQL string to be executed would usually be something like ...

" ... and p2c.categories_id = " . implode(',', $array_name);

but obviously you'd need to translate that into what's needed in the dodgy code that's been added to your site.

[schoolboy]

... all the above related to using cheap "Bollywood Freelancers" who are very good at delivering rubbish!

You should see some of the stuff we're asked to fix! (And I am a relative novice at PHP)...

[DrByte]

Please click the "Reply" button below, and answer all the questions asked in the "Posting Tips" section.