Optimising Mysql Quirries
Required skills: Programming, Drupal, MySQL, E-Commerce, osCommerce
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:
---------
http://www.scriptlance.com/projects/1199539444.shtml
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(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 | client1098.com:43027 | 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(s.status, 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 | client1098.com:43075 | phobos26_klus | Sleep | 1087 | | NULL |
| 1424 | phobos26_klus | client1098.com:58340 | 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 | client1098.com:58361 | 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!
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:
---------
http://www.scriptlance.com/projects/1199539444.shtml
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(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 | client1098.com:43027 | 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(s.status, 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 | client1098.com:43075 | phobos26_klus | Sleep | 1087 | | NULL |
| 1424 | phobos26_klus | client1098.com:58340 | 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 | client1098.com:58361 | 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!
Posted In:MySQLProgrammingE-CommerceDrupal |
Related projects:
Php mysql codeigniter web developer
l will help.
3. should be comfortable with writing large mysql queries and optimising large queries.
4. Candidates should be a freelancer who can work at flexible hours.
5. Should be based in India.
6. Must have skype/gtalk/yahoo for communication.
Please bid only if you satisfy all the above criteria.
Mention the word "front-mvc" in your bids/pmbs to indicate that its a real bid and not one made by a bot.
3. should be comfortable with writing large mysql queries and optimising large queries.
4. Candidates should be a freelancer who can work at flexible hours.
5. Should be based in India.
6. Must have skype/gtalk/yahoo for communication.
Please bid only if you satisfy all the above criteria.
Mention the word "front-mvc" in your bids/pmbs to indicate that its a real bid and not one made by a bot.
Move mysql database powweb.com to godaddy.com
Move mysql database powweb.com to godaddy.com Database is less then 10 Megabytes. Project needs to be done very soon.
Xzero Classifieds Script Modifications Php/mysql 05/30/12
I'm looking for somebody that is very fluent with PHP & MySQL and has experience on the script from Xzero Classifieds.
The subject of activation link is not displaying correctly in mail inbox. (the link of contact form and email link too). Please find the attached file to see problems.
The category list on post ads section needs to be ordered by row, not by column.
Let me know if u can work.
The subject of activation link is not displaying correctly in mail inbox. (the link of contact form and email link too). Please find the attached file to see problems.
The category list on post ads section needs to be ordered by row, not by column.
Let me know if u can work.
MySQL Database Recovery
br>Some resources:
1. Boot the server to rescue mode using the Recovery Tool located in your 1&1 Control Panel:
"What is the Recovery Tool?"
http://faq.oneandone.com/dedicated_servers/root_server/linux_recovery/1.html
2. How do I mount my drives in the Rescue System?
http://faq.1and1.com/dedicated_servers/root_server/linux_recovery/7.html
3.Back up any important data:
You should already know mysql dump
1. Boot the server to rescue mode using the Recovery Tool located in your 1&1 Control Panel:
"What is the Recovery Tool?"
http://faq.oneandone.com/dedicated_servers/root_server/linux_recovery/1.html
2. How do I mount my drives in the Rescue System?
http://faq.1and1.com/dedicated_servers/root_server/linux_recovery/7.html
3.Back up any important data:
You should already know mysql dump
Design new Web Interface (PHP/MySQL)
Login/Logout/Reset Password, ... The users will have different roles once logged in. The user types will range from end users to agents, administrators and managers. Once logged in (and based on their assigned roles), the website users should be able to perform tasks such as registering new users, displaying and editing user information, performing transactions, ... among other tasks.
Deadline for choosing a Freelancer: 2 weeks
Deadline for choosing a Freelancer: 2 weeks
mysql database connectivity
by high level product category and stored for 90 days in the CRM before being erased in the CRM.
You will need to have a strong working level of mysql databases, php and have worked with Sugar CRM (as there will be some customization of fields). We also prefer that you have experience with Webid auction platform.
We are looking for a simple open source solution that can be customized to our needs in a short time period.
You will need to have a strong working level of mysql databases, php and have worked with Sugar CRM (as there will be some customization of fields). We also prefer that you have experience with Webid auction platform.
We are looking for a simple open source solution that can be customized to our needs in a short time period.
PHP online form and MySQL database
requirements carefully before bidding.
Need 12 online forms created in php integrated with mySQL databse.
The forms have to be editable and deletable.
An edit button will be placed on the bottom of each form
When viewing the form users will be able to click on edit and modify the form.
You have to have proven php mysql programming experience. Submit sample php code with bid and URL to work that show php mysql work.
Need 12 online forms created in php integrated with mySQL databse.
The forms have to be editable and deletable.
An edit button will be placed on the bottom of each form
When viewing the form users will be able to click on edit and modify the form.
You have to have proven php mysql programming experience. Submit sample php code with bid and URL to work that show php mysql work.
PHP and mySQL reconfiguration
I require rework of an existing website, specifically the PHP/mySQL interface. I can supply the current code and need a person who can liaise with our partner to ensure that the PHP page receives input data, sends an accurately shaped email as well as inserting a record into the database.
JavaScript analysis , modefecation / php / mysql
hat , just pick a good one.
Now in a few words:
You need to understand how the javascript files works, be able to edit the JS file to send the request to our servers , decrypt the request via php , save it in mysql .
And make a function do the calculation to generate the heatmap and the other things you may see in the website.
The mean part is the JavaScript , the other things is just an easy stuff.
Put your price .
Now in a few words:
You need to understand how the javascript files works, be able to edit the JS file to send the request to our servers , decrypt the request via php , save it in mysql .
And make a function do the calculation to generate the heatmap and the other things you may see in the website.
The mean part is the JavaScript , the other things is just an easy stuff.
Put your price .
Looking for a PHP, JS and MySql web developer
I am looking for a freelance web developer who can develop my website. I require to develop this in JS, PHP and MySql. I can provide some more information if require. I am welling to pay fix amount for this job. I can also deliver my web page rough design. That is a job section including job form, listing page, job description page, user panel control and backend for this part.
PHP / MYSQL Twitter/RSS Feed Aggregator / Portal
We are building a destination portal for a particular industry and need a programmer to help us create a website.
We will build the site using the following technologies:
PHP / MYSQL
Javascript, CSS
JSON
RSS
There will be some admin screens. We are open to using a third party CMS like wordpress to accelerate the release of the website.
We will build the site using the following technologies:
PHP / MYSQL
Javascript, CSS
JSON
RSS
There will be some admin screens. We are open to using a third party CMS like wordpress to accelerate the release of the website.
Make Mysql Php Website Work Offline On Ios
I have a mysql / php website where i submit reports to the database through a web form, the form takes some data from the database first (i.e. name of the place i am in, user etc,
I need this to work offline then sync or upload to the database once I have a connection.
I need this to work offline then sync or upload to the database once I have a connection.
PHP MySQL developer required for small jobs
We provide you with regular work, On per project basis.
You must be Fluent in PHP and MySQL work. Must know working to tight deadlines.
Be positive and can provide work in time.
Get In touch and lets start.
Regards
S/Kype smrazi007
You must be Fluent in PHP and MySQL work. Must know working to tight deadlines.
Be positive and can provide work in time.
Get In touch and lets start.
Regards
S/Kype smrazi007
Highcharts - javascript, PHP, MySQL ninja!
is a charting library written in pure JavaScript:
http://www.highcharts.com/
I send you all the files, structure, SQL and graphs. You just need to:
1 - grab the data from mysql table
2 - send it to 'file.js' using JSON
3 - xAxis: days in format DD-MM-YYYY
4 - yAxis: time (HH:MM)
All this files needed in attached - TAKE A LOOK BEFORE BID!
Quick and easy work to a MySQL / JSON / Javascript specialist!
http://www.highcharts.com/
I send you all the files, structure, SQL and graphs. You just need to:
1 - grab the data from mysql table
2 - send it to 'file.js' using JSON
3 - xAxis: days in format DD-MM-YYYY
4 - yAxis: time (HH:MM)
All this files needed in attached - TAKE A LOOK BEFORE BID!
Quick and easy work to a MySQL / JSON / Javascript specialist!
monodevelop mysql C# configuration
I have a aplication on C#, it compiled and work (windows), now i need run it on a vps(ubuntu), but it have a problem with mysql configuration with mono develop, i post a screenshot of the error.
I need that my code work on the vps.
The lower bid win, for start inmediately.
I need that my code work on the vps.
The lower bid win, for start inmediately.
Finishing Touches On Php Mysql Cms
place it below the plugs, the content won't show. Should be an easy fix for that.
3. The trade part of my script needs a fix/addon. The out.php file needs to trim the www. From the outgoing url So it doesn't create two domains.
Also I need submit page added. The submit page will allow domains with a minimum number of unique hits and a minimum ratio to add a plug. Database to enter the plugs already exists, as does the tracking script. Just need a submit form.
3. The trade part of my script needs a fix/addon. The out.php file needs to trim the www. From the outgoing url So it doesn't create two domains.
Also I need submit page added. The submit page will allow domains with a minimum number of unique hits and a minimum ratio to add a plug. Database to enter the plugs already exists, as does the tracking script. Just need a submit form.
moving mySQL
We're moving our website to another server, and we need to transfer PHP app with mySQL database from old server to new one. The database name probably has to change so someone has to look up the code and probably make the necessary changes. That PHP has only 6-7 pages so it's not something elaborate, it's just a list of files database and the PHP pages display them. We need this done fast, like in couple of hours max.
SmartJobBoard Smarty PHP mySQL
I need a provider with experience using smartjobboard.com software. This software uses Smarty, PHP, and mySQL. You should be an expert with the job board customization. User manual is available here http://smartjobboard.com/wiki/Smart_Job_Board_User_Manual
Please provide sample of prior SmartJobBoard customization experience. Thanks.
Please provide sample of prior SmartJobBoard customization experience. Thanks.
PHP script to MySQL database and integrate with PayPal
the same way the existing PHP script is).
(g) Integration with Paypal should use the standard APIs and Paypal includes as provided on the Paypal development site / sandbox. The only modification to the includes should be the configuration variables and efforts should be made to keep as much as possible to Paypal's example use of the APIs for future maintainability.
(h) A copy of the existing HTML form and PHP script is attached.
(g) Integration with Paypal should use the standard APIs and Paypal includes as provided on the Paypal development site / sandbox. The only modification to the includes should be the configuration variables and efforts should be made to keep as much as possible to Paypal's example use of the APIs for future maintainability.
(h) A copy of the existing HTML form and PHP script is attached.
Mysql And Php Website Work.
I need someone to optimize mysql database queries. I have a website that has a large Mysql database and the database is causing my website to go down. I'm using pricetapestry affiliate marketing software and it takes days to import datafeeds into the database because of the large database. I also would like a new look to my website. I would like to keep the same CMS.
