We are an osCommerce shop who has just been informed by our Host that ourt database needs to be optimized to maintain our shared server. This request is to fix the below noted query (and others submitted with this ticket) as well as optimizing the ENTIRE sites mySQL database. We need this to be resolved ASAP as the host is threatening to suspend our account permanently.
Note from Host
"What I noted specifically below are several long running datanase queries, usually caused by either an unoptimized database or one that has outgrown a shared environment."
89644 | xxxxx_xxxx | localhost | xxxxx_xxxx | Query | 235 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF([login to view URL], s.specials_new_products_price, NULL) as specials_new_products_price, IF([login to view URL], s.specials_new_products_price, p.products_price) as final_price from ((products p) left join manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c,products_attributes pa where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%shaft%' or p.products_model like '%shaft%' or m.manufacturers_name like '%shaft%' or pd.products_description like '%shaft%') ) order by pd.products_name limit 0, 18 |
## Deliverables
Here are all the queries the Host submitted that were causing issues, but others may exist. With this ticket, we intend to have the ENTIRE database optimized which may include updating other queries.
89644 | xxxxxxx_xxxx | localhost | xxxxxxx_xxxx | Query | 235 | Copying to tmp table | select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, pd.products_description, p.products_price, p.products_tax_class_id, IF([login to view URL], s.specials_new_products_price, NULL) as specials_new_products_price, IF([login to view URL], s.specials_new_products_price, p.products_price) as final_price from ((products p) left join manufacturers m using(manufacturers_id), products_description pd) left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c,products_attributes pa where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%shaft%' or p.products_model like '%shaft%' or m.manufacturers_name like '%shaft%' or pd.products_description like '%shaft%') ) order by pd.products_name limit 0, 18 |
| 189945 | xxxxxxx_xxxx | localhost | xxxxxxx_xxxx | Query | 212 | Locked | update products_description set products_viewed = products_viewed+1 where products_id = '2337' and language_id = '1' |
| 190437 | xxxxxxx_xxxx | localhost | xxxxxxx_xxxx | Query | 146 | Locked | SELECT products_name as pName
FROM products_description
WHERE products_id='1763'
AND language_id='1'
LIMIT 1 |
| 190852 | xxxxxxx_xxxx | localhost | xxxxxxx_xxxx | Query | 101 | Locked | SELECT products_name as pName
FROM products_description
WHERE products_id='663'
AND language_id='1'
LIMIT 1 |
| 190950 | xxxxxxx_xxxx | localhost | xxxxxxx_xxxx | Query | 91 | Locked | select pd.products_head_title_tag, p.products_id from products_description pd left join products p on pd.products_id = p.products_id left join products_to_categories p2c on pd.products_id = p2c.products_id where p.products_status = '1' and pd.language_id = '1' and p2c.categories_id = '0' order by pd.products_head_title_tag Limit 5