293189 Optimising mySQL quirries

Hello, I've find a older "project" and I think my problem is similar. I'm running an OScommerce site and last times I'm having lots of problems because server takes lots of time to execute mysql requests. All the other soft in this server runs nice (drupal, Cpanel, Web Host Manager, simple html/php pages...). I suppose the problem is because I use an older OSCommerce snapshot (from 2002, August). I can not update it because of multiple features I added to fit better to my business. OK, here I have 3 options: 1) Improve ONLY OSCommerce code to skip "bad" mysql requests using better ones (it's possible that actual oscommerce code help for it) 2) Update to the latest OSCommerce, and migrate most of the personal improved features (as minimum, the most important) 3) Migrate to Ubercart, a Drupal ecommerce soft. I want people to do one of these 3 options. Could you tell me how much cost if YOU do one of them? Regards, Joan This is the older project I've find here: --------- [login to view URL] Currently we have a lot of problems with the mySQL database. Due to heavy load and visitors the server is giving Server 500 Errors. According to the provider is because the mySQL quirries. They must be re-written or optimized. Here is the letter of the provider. ------------------------> mysql> select p.products_image, pd.products_name, p.products_recent_sales, p.products_id, p.products_recent_sales,p.manufacturers_id, 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_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '3504' order by p.products_recent_sales desc, pd.products_name desc limit 0, 20; That is the MySQL query that is 'killing' the site. That is a developer issue not a server problem. It is not the number of queries but the type of the query that is causing the server load and the query from above is the bottle-neck. That can be fixed only by those who have created the site. My advise is to make your developers fix this Alderd because the other option is to move to another provider with a possibly "faster" server but that would not matter when the problem is in the software. You may end up paying more for hardware than you should all because of a MySQL query. Right now one of our administrators continues to check what else could be the problem but for now that is what he has found. I'd hate you loose you as customer but in this case there is not much we can do - only to point you to the actual problem. Yes it would be our word against the word of your developers but what I have stated above is our professional opinion about this problem. Let me know if we can assist with anything else and if there are any problems so we can help. Thank you. p.s. If the administrator discovers anything else of interested we will let you know immediately. --------------------> and: The conclusion from the investigation is the fact that because of few slow MySQL queries the whole site suffers. Because of those queries the rest cannot be completed thus the slow site performance or occasional errors. I have already pasted one of those queries which is a join of very big tables (with a lot of entries), another such query is the following: | 1061 | phobos26_klus | [login to view URL] | phobos26_klus | Query | 113 | Copying to tmp table | select p.products_id, pd.products_name, p.products_weight, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF([login to view URL], s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, m.manufacturers_name from products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id and pd.language_id = '4' left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_price > 1 order by p.products_price, pd.products_name limit 0, 20 | | 1083 | phobos26_klus | [login to view URL] | phobos26_klus | Sleep | 1087 | | NULL | | 1424 | phobos26_klus | [login to view URL] | phobos26_klus | Query | 57 | Locked | update products_description set products_viewed = products_viewed+1 where products_id = '49904' and language_id = '4' | | 1431 | phobos26_klus | [login to view URL] | phobos26_klus | Query | 3 | Locked | select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '571' This query and the one I have posted earlier are anything but optimized or effective. They use a lot of CPU power just to display a certain number (which can be obtained by other means - like with much simpler MySQL query) or to duplicate a certain entry/page (another slow MySQL query was copying information to e /tmp table). In other words - due three slow MySQL queries the site performs poorly when under load. Those queries can and should be optimized by your developers and regardless of the price I do believe it would be the cheapest and fastest solution. Such a big and popular site should relay on simpler MySQL queries since they are way faster and would not cause the bottle-neck which appears when the slow ones are executed. Here's another example of what happens. The site is working just fine, more and more visitors come, at one moment one of the slow MySQL queries (for instance the join table one) is executed but it takes very long to complete, while running other MySQL queries are waiting in the queue for their turn, the longer the first query runs the longer the rest are waiting and new ones enter in the queue as well. This is what's causing the slow performance. Sadly we don't offer any load-balancing solutions but again my advise is to reconsider this. The performance you would gain from that would not be big and in one time even that type of hardware would not be sufficient to compensate for a software bottle-neck. The more visitors you have, the more queries there would be, the slower the site will perform - unless those queries are optimized that is what is going to happen. Hiring developers for that work is the best and cheapest solution in both the long and the short term since it will ensure that only fast('light') MySQL queries are executed. No changes were made on the server from our side and just to ensure that the 500 errors are not caused by another issue I asked Jack to recompile a new version of SuExec with higher limits for running processes (as high as the machine can handle). Basically that is the problem and it must be fixed as soon as possible. Forgive me for my rather rude language but I don't trust your developers because of their statements. Those type of MySQL queries are simply inadequate and I'm sure if you look for another developer team or any other 3rd party which can provide you with a second opinion you they will tell you the same thing as us - making a MySQL query which (metaphorically said) takes out the information on 100 book pages just to select and use few words or count the number of a given word used - is anything but the best way to make a smoothly working site. I really hope that this information would help you to both solve the problem and make the right choice. If you still want to move to a faster hardware please let me know I may be able to find hosts with a good reputation and quality services (but just have in mind that they won't be the cheapest ones and I also advise to test their services for a month before completely switching to them). Server specifications: 2 x Xeon Clovertown 5310 Quad 4 GB RAM 2x500 GB SATA Hard Drives Shoppingcart: Modified OSCommerce Statistics: mySQL database: 155MB Quirries: 45,000,000 per day traffic: 6GB per day with 1800 unique visitors We're looking for someone who has an excellent record on these kind of issues. We need the issue solved and the speed increased. When you have another suggestion please let us know. Further questions we're happy to answer. Thanks!