Friends please review this simple query. This query is taking too much time to execute and we are getting 'CPU Quota Exceeded' error at regular intervals. In hosting server logs sometimes it takes more than 10 secs to execute. I asked hosting support and they said it is because your code is not efficient. Please advice me what's wrong with this query This is not a crucial query so i disabled it. Now another query is taking too much server time. These queries were not in slow queris log before today's date. See the log blew. Please suggest what's wrong in queries. Thanks.
It would help if you could post the table structure. Also if your tables are very large indexes should be used (for example product_type in the 1st query is probably a good candidate for indexing)
Also might need to see the rest of your code. I wouldn't think that query would cause that much problem. Quite possibly the query is contained in a loop that does not stop, causing a crash?
Also keep in mind slow queries in the log can be produced by a laggy server. Slow queries are determined by how long they're running. What happens if your host's server is really slow thus what should be a fast query is running for 30 seconds because there is no CPU time. But as for the question at hand fields where you're doing a lot of where clauses on and there are groups of them. So for example if you had a users table and there was a group_id it be logical to index that. So the obvious one in your case is product_type but without a full db structure I could not tell you if others should be using them. You'd be just how surprised indexes can help improve performance. I've seen web sites with 0 indexes causing 30 second queries constantly. Add just one index and suddenly the mysql usage is close to 0 and queries are being executed instantly.
Thanks for your suggestions. You people are right. I did not create index for product_type. Will create an index for this but if you see above log there is also a 'create temporary table' query it is also taking too much time. you can't say it is index problem. What it can be.
You need to create an index for product_id AND product_type on the first query. And index for gid in the second query. And for the last query, you need to look at the query before that one. I am guessing that you are trying to do some ORDER BY/LIMIT/JOIN on the table You need to look at ALL your queries and make sure you have the appropriate indexes on all the tables and then worry about temp tables. Also on a busy(ish) site some queries have to wait for others to complete, (hence the locks on the table) Have a look at the IGNORE LOW_PRIORITY and DELAYED keywords. But before doing any of that I would do ALL the indexes first. FFMG