frequent / resource intensive calls to MySQL databases - please help

[jennypretty]

Hello,

I have hostgator hosting account for over a year now. And, I just recived a message from them saying that my all my websites have
been suspended. Here are the message:

Your account was suspended for a Terms of Service violation: Excessive
MySQL/CPU use. A sample of the log is attached at the end of this
message. Many times, excessive MySQL activity is caused by (a) a
long-running process that locks a table, causing other queries to back up, (b) a
query that is not optimized ][example: select all from ... and involving
a large or complex query], (c) huge table copies/maintenance during
peak hours. Please respond and let us know how you will prevent this
situation from recurring, so we can restore access. Thanks.

After that I sent them a message to restore my account so I can fix the problem. And, they sent me this reply:

You may want to work with your scripts so that they do not make
frequent / resource intensive calls to MySQL databases. Please let us know if
you have any further questions -- we will be able to restore access as
soon as you can inform us of the changes you are going to make.

Can you please show me how do I modify my script so that they do not make frequent / resource intensive calls to MySQL databases???

Please help.

Jenny.



== notes ==
| 13979297 | ujennydb | localhost | jennydb
| Query | 136 | Copying to tmp table | SELECT t1.*,
COUNT(DISTINCTROW(t2.`id`)) clicks, '0' id_editor_edit,COUNT(DISTINCTROW(t3.`id`))
comm |
| 13979362 | umydbname | localhost | mydbname
| Query | 118 | Copying to tmp table | SELECT t1.*,
COUNT(DISTINCTROW(t2.`id`)) clicks, '0' id_editor_edit,COUNT(DISTINCTROW(t3.`id`))
comm |
| 13979368 | udb2 | localhost | db2
| Query | 115 | Copying to tmp table | SELECT t1.*,
COUNT(DISTINCTROW(t2.`id`)) clicks, '0' id_editor_edit,COUNT(DISTINCTROW(t3.`id`))
comm |

[mlseim]

We're going to need more information ... for example,
What is the MySQL database for ... what script (a blog? an online store?).

Whether or not you've installed an "approved" script, like WordPress, or is
this a script of your own design?

What kind of website, music downloads? file downloads? and how many
visitors you've been getting lately.

[jennypretty]

Hello,
It is a directory script from Esyndicat software (free version) and softbiz dating software. I have some of the spammers who keep
posting on directory, but I deleted the spamming posts every week.

Do you want me to send the db script?

thanks.

[mlseim]

You answered your own question ....

Spammers are hammering your database. It doesn't matter if you delete
them every week, the fact is, they are executing the database each time
they access it. That's what your webhost is telling you.

You need to stop the spamming by using a user-register method, captcha,
or some other sort of way to stop the spamming.

If you can show your webhost how you are regulating the users, they will
be satisfied and open your account. You'll have to hire a programmer to
either fix the existing script, or you'll have to find a better script that has
some security protection (which probably won't be freeware).

[jennypretty]

Thanks.
I told the host admin that I'll delete all of these databases.
Because the website does not make money, just a few adsense code to pay for the host and domain so I can't afford to hire a programmer to fix the script.

thanks.