SELECT loop too slow

[andreas_boehmer]

Hi guys, I am having major problems getting a loop of SELECT statements to work. I am fairly sure it is caused by the amount of data that has to be processed, but maybe I am wrong.

Here a bit of background information on the problem I am currently trying to solve:

I am working on a job search engine, that has got jobs sorted by different industries. There are about 150 industries holding 15,000 jobs. I have written a PHP script that is meant to spit out a report on how many jobs there are per industry and how many users have applied for jobs per industry, etc.

Unfortunately the script runs forever and mostly times out with an error "Lost connection to MySQL server during query".

The script does something like this:

SELECT id FROM industries

LOOP THROUGH ALL INDUSTRIES
SELECT COUNT(*) FROM jobs WHERE industry=[industryNo];
SELECT COUNT(*) FROM applications, jobs WHERE jobs.id=applications.jobID AND industry=[industryNo];
END LOOP

Obviously all of this written in proper PHP syntax.

Now the question I have is if there is anyway to improve the performance so mySQL returns the data faster? Is there anything I can do to the mysql database to improve speed, or should I rewrite the SQL statement,... I am sure there must be a way of doing this, but I cannot come up with any better solution.

Thanks heaps!