But is not that why we love this job? Due to the challenges it bring
I've reviewed the thread again after Rémon was kind enough to merge your topics.
I'll go out on a limb and say that you are not used to work with high load or high dependency websites.
After reviewing your first query, I'm 100% sure your problem can be fixed with de-normilization and with proper use of cache techniques.
Query in main post:
-You run two LEFT OUTER queries which count how many applications the job has received and one who count featured (not sure what this is for). Both these counts should also have been a separate column on the jobs table. Which is increased/decreased when a record is added/removed in said tables. This can be handled either by server side or triggers in the database.
-Your sub query is strange, you check to see if the job is only available on one location or multiple, but also pull the postcode etc. While the query is GROUP BY the job id, meaning that if there is more than one location you have no idea which postcode etc. your pulling. Which makes it look like if its in more than one location, you only show "Locations throughout the UK" for the user. With other words, this can ALSO be added as a column to the jobs table to keep track of, similar to what is mentioned above.
-In addition the pulling of the locations the job is available in should be moved outside the initial query, even if you want to pull all locations. Lets say you want to pull all locations per job, and you pull multiple jobs at a time. What you would do is: First loop over the jobs and in addition store the job ids in an array, then after pull all locations used across the jobs. With other words everything is done with two queries.
-You have INNER JOINS which you don't use, example: jobs_industries and jobs_job_types Make certain you remove anything from the query if you don't need it.
-You are using ENOM's or at least string keys like job status "on". Stop doing that and start using integers, that will make it slightly easier to convert to other databases in the future if needed.
-I assume you do not store the thumbnail image as a blob, referring to the companies table "logo_thumbnail" column. If you do stop with that, its very ineffective. In the event you store only the image file name, stop doing that as well! Instead use the company id for the file name, i.e. "ID_thumb.jpg" etc.
-You have a LIMIT on three records, just first running this query first:
SELECT job_id FROM jobs INNER JOIN companies as comp ON (jobs.company_id=comp.id AND comp.status='Active') WHERE jobs.status='on' ORDER BY jobs.date_posted DESC LIMIT 3
And then after do WHERE jobs.job_id IN (1, 2, 3) I.e. using the results from the first query, or even using it as a sub query would most probably make that query fast again.
-Though the other points I mentioned above I would have done here as well.
What you need to understand is that there is two kinds of people that work with Databases, the ones who want everything normalized, and the ones who understand that while normalized is the ultimate goal, it just does not work to have everything normalized for a high load or high dependability system.
With other words, a normalized database come at the cost of speed and required resources.
Now, with that said keep your database model as normalized as possible, as you can apply a lot of caching on the expensive parts as well. Even separating a larger query into two or more queries might change the application from taking 3 seconds, to take 0.00000005 seconds again even with more queries.