Page 1 of 1

PHP please help, I just got taken down by my hosting company

Posted: Wed Mar 24, 2010 12:19 pm
by defroster
Hello, I am not a programmer but I just got this from godaddy. How can I change this problematic script? Thanks /D. Froster

It has come to our attention that your hosting account, specifically the pnktn database is causing the shared resources to be over-utilized. This, in turn, affects the usage by other customers.
 
We have disabled your database to return the server to normal usage. To re-enable your database, you will need to correct the following query:
 
-Problematic query:

Code: Select all

 
SELECT * , count(*) as count FROM pligg_links , pligg_categories
WHERE category_lang='en' AND category_id=link_category GROUP BY
link_category ORDER BY category_name ASC
 
EXPLAIN:
id
select_type table type possible_keys key key_len ref rows Extra
1
PRIMARY pligg_categories ALL 31 Using where; Using
temporary; Using filesort
2 DEPENDENT SUBQUERY pligg_links ALL
4619 Using where
 
This query examines 143189 rows
 
Problematic query:

Code: Select all

 
SELECT link_id FROM pligg_links WHERE
link_status='published' ORDER BY (SELECT SUM(vote_value) FROM
pligg_votes WHERE vote_link_id=link_id) DESC LIMIT 15,5
 


EXPLAIN:
id
select_type table type possible_keys
key key_len ref rows Extra
1
PRIMARY pligg_links ALL
4619 Using where; Using filesort
2 DEPENDENT
SUBQUERY pligg_votes ref link_id link_id
4 pnktn.pligg_links.link_id 37
 
This
query examines 170903 rows, which is unacceptable in shared hosting.
 

Re: PHP please help, I just got taken down by my hosting company

Posted: Wed Mar 24, 2010 12:34 pm
by Sofw_Arch_Dev
Can you explain what your queries are attempting to do?

Re: PHP please help, I just got taken down by my hosting company

Posted: Wed Mar 24, 2010 12:40 pm
by defroster
I am not a programmer, I just know very little. But they get links from the database and present them in order by vote of popularity which the users on the website have done.

Re: PHP please help, I just got taken down by my hosting company

Posted: Wed Mar 24, 2010 1:49 pm
by mikosiko
defroster wrote:I am not a programmer, I just know very little. But they get links from the database and present them in order by vote of popularity which the users on the website have done.
The second query seems to be doing that.... what about the first one?

you could try to re-write the second one in this way and test performance:

Code: Select all

 
SELECT a.link_id, SUM(b.vote_value) AS xxx FROM pligg_links a JOIN pligg_votes b ON (a.link_id = b.vote_link_id)
WHERE link_status='published' 
GROUP BY a.link_id
ORDER BY xxxx DESC LIMIT 15,5
 
you must check also that you have INDEXES for both tables in the columns a.link_id and b.vote_link_id respectively.


For the first one... always is better to apply the count() over and indexed field.. so.. you can use

Code: Select all

 
SELECT * , count(link_id) as count FROM pligg_links , pligg_categories
WHERE category_lang='en' AND category_id=link_category GROUP BY
link_category ORDER BY category_name ASC
 
even thou I dont understand why you are doing a JOIN in this case... doesn't looks necessary... I could be wrong

Re: PHP please help, I just got taken down by my hosting com

Posted: Sat Jul 24, 2010 11:10 am
by defroster
Thanks I really appreciate the help but all this is a bit more complicated than I thought. THanks