Jump to content

jezza52

New Members
  • Posts

    2
  • Joined

  • Last visited

jezza52's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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
  2. Hello Folks! This is the first time ive posted for help with PHP/MySQL, I usually always find the answer to what im looking for on various forums but I think this problem is a bit to specific to solve third hand... I'll try and explain the best I can... I run a job board which uses RSS feeds to distribute jobs, we are migrating to a new platform so ive got to redo the rss feeds. One of the feeds needs the job category, which is not located in the table where the job, salary, url, description and so forth. To find which category the job is relevant to there is a jobs_industries table but this also only contains a further reference; Full texts job_id company_id industry_id Edit Edit Copy Copy Delete Delete 84578 10772 114 Edit Edit Copy Copy Delete Delete 84580 10772 114 Edit Edit Copy Copy Delete Delete 84740 10810 20 Then finally we have the table that has the job categories under 'Industries' and the data is as follows; Full texts id industry industry_description industry_url parent show_type show_order Edit Edit Copy Copy Delete Delete 1 Accounting / Audit / Taxation NULL accounting-audit-taxation 1 parent 1 Edit Edit Copy Copy Delete Delete 2 Accountant NULL accountant 1 child 1 Edit Edit Copy Copy Delete Delete 3 Accounting Clerk / Supervisor NULL accounting-clerk-supervisor 1 child 1 So heres the bit I cant work out, Ideally I want to populate another colunm in the jobs database so the relevant job category is displayed (Also the pupose of this is because the RSS feed generator only works within one table. I currently use this php script to populate the job_location column I added within jobs; <?php $con=mysqli_connect("localhost","****","************,"*******"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } mysqli_query($con,"UPDATE jobs INNER JOIN jobs_locations ON (jobs.id = jobs_locations.job_id) SET jobs.job_location = jobs_locations.display_name"); mysqli_close($con); ?> Could anyone help me create a similar script for a new column in the jobs table whereby the job categories are inserted, the above code ensures the unique ids are matched so the new column data is inserted correctly, the thing is I can work out how to reference the job category through 2 tables.. Thanks in advance.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.