Really badly coded PHP MySQL search query (4.1 million for 2500 records!)

[jezza52]

Hello guys!

 

I would really appreciate some help with this ridiculus search query I have running on a live job board at the moment. There are 2500 live jobs posted within the last 40 days and if a user does a search on the job board that inludes all jobs it takes around 60 seconds to bring up the results, its causing huge problems and many users just think the website is broken.

 

I have attempted to try and fix the slow query myself (its based on a large amount of inner/outer joins). The job board was saving data in the database in a very illogical way whereby to find specifics such as industry, location, employer it had to reference a second table and sometimes a third to return the actual data. I have written some scripts to carry this out and cross reference on an hourly basis and copy the data to colums within the actual job table so I have added further colums to 'jobs' for 'job_industry', 'job_city', 'job_state' and 'job_recruiter'. The problem is I do not know how to alter the complex search query to stop it cross referencing these fields as I believe this is where the problem is. I will attach the search.class PHP file, please note the only part of this file I have altered since the original is the addition of:

 

"            ".TABLE_PREFIX."currentjobs.job_industry,
            ".TABLE_PREFIX."currentjobs.job_city,
            ".TABLE_PREFIX."currentjobs.job_state,
            ".TABLE_PREFIX."currentjobs.job_recruiter,

"

 

I then tried to modify the search query myself by removing some of the inner join queries and then modifying the template file to include the above table prefixs (the current template is no longer using the join query for these items above and its working fine).

 

I have checked the MySQL slow queries log (which is 62mb and a nightmate to open in textpad) and the following is reported:

 

"Time: 140317 23:34:50]
# Query_time: 52.248147  Lock_time: 0.000229 Rows_sent: 2341  Rows_examined: 4101782
SET timestamp=1395099290;
select
            currentjobs.id as job_id,
            currentjobs.job_title,
            currentjobs.job_url,
            currentjobs.job_salary,
            currentjobs.job_ote,
            currentjobs.job_perks,
            currentjobs.job_description,
            currentjobs.date_posted,
            companies.company_name,
            companies.company_url,
            companies.logo_thumbnail,
            count(applications.id) as applications,
            count(featured_jobs.id) as featured,
            jt.job_type_display,
            ind.industry_display,
            l.display_name,l.postcode,l.display_county
            from currentjobs
            inner join companies on companies.id = currentjobs.company_id
            inner join
                (select job_id,
                    CONCAT_WS(', ', GROUP_CONCAT(industry)) as industry_display,
                    industry_id from jobs_industries
                    left join industries on industries.id = jobs_industries.industry_id
                    group by job_id
                ) as ind on ind.job_id = currentjobs.id
            inner join
                (select job_id,
                    CONCAT_WS(', ', GROUP_CONCAT(job_type)) as job_type_display,
                    job_type_id from jobs_job_types
                    left join job_types on job_types.id = jobs_job_types.job_type_id
                    group by job_id
                ) as jt on jt.job_id = currentjobs.id
            inner join
                (select job_id,
                    concat(display_town, ', ', display_county) as display_name,
                    postcode,
                    display_county,
                    location_id from jobs_locations group by job_id
                ) as l on l.job_id = currentjobs.id
            left outer join applications on applications.job_id = currentjobs.id
            left outer join featured_jobs on featured_jobs.job_id = currentjobs.id
            where currentjobs.status='on' group by currentjobs.id order by featured desc, date_posted desc;"

 

 

I am uanble to upload the .tpl file so ill paste part of it below, this is the part I have altered to stop the website using results from the inner join queries:

 

                                        <td><b>:</b><a href="{$smarty.const.SITEURL}/{$settings.company_directory}/{$records.company_url}">{$records.company_name}</a></td>
                                </tr>
                                <tr>
                                        <th>Location</th>
                                        <td><b>:</b>{$records.job_city}, {$records.job_state}</td>
                                </tr>

                                <tr>
                                        <th>Category</th>
                                        <td><b>:</b>{$records.job_industry}</td>
                                </tr>
                                <tr>
                                        <th>Type</th>
                                        <td><b>:</b>{$records.job_type_display}</td>

                                </tr>
                                <tr>
                                        <th>Added on</th>
                                        <td><b>:</b>{$records.date_posted|date_format:"%b %e, %Y"}</td>
                                </tr>
                                <tr class="lastrow">
                                        <th>Salary</th>
                                        <td><b>:</b>{format_currency value=$records.job_salary settings=$settings}</td>

 

I would really appreciate some help on improving this search query, im pretty much novice at PHP, I can write some basic scripts and modify exisiting scripts reasonably to get the results I want but this is a little bit over my head, and its a live website with a vast number of daily users that I dont really want to interupt.

 

** Also one more item to add, the current jobs table has over 100,000 rows, I initially thought it wa due to this that the search was slow so ive written a script to copy all rows with status = 1 from 'jobs' to 'currentjobs' so at the moment 'currentjobs' only has around 2500 rows, it seems this was actually unnecessary as it has not improved the search whatsoever so I think I should probably change this back and edit search.class.php?

 

Thanks guys

search.class.php

Edited March 19, 2014 by jezza52

[fenway]

Just post the EXPLAIN and we'll see how bad it is.