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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

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

Post 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.
 
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

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

Post by Sofw_Arch_Dev »

Can you explain what your queries are attempting to do?
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

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

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
defroster
Forum Commoner
Posts: 49
Joined: Wed Mar 24, 2010 12:05 pm

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

Post by defroster »

Thanks I really appreciate the help but all this is a bit more complicated than I thought. THanks
Post Reply