RedJumpsuit

jobberBase custom development and support

 

Code School

Experimenting on Full-Text Search

today i had time to look closely in to the Search() function on ‘class.Job.php’, and i wanted to see how Full-Text Search can improve the search performance of jobberBase when searching for a couple thousand jobs. i don’t have that much data to test yet so i am hoping the good people reading my posts can help me with testing and measuring any improvement.

first things first. back up your ‘jobs’ and ‘cities’ table, you’ll have a fall back if anything breaks.

so let’s start with running these sql lines on your ‘jobs’ and ‘cities’ tables in your phpmyadmin:

ALTER TABLE `jobs` ADD FULLTEXT (
`title` ,
`description` ,
`company` ,
`outside_location`
);
 
ALTER TABLE `cities` ADD FULLTEXT (
`name`
);

then open up /_includes/class.Job.php and comment out the whole ‘public function Search($keywords) {}’ block.

then add this below it:

// Search for jobs
public function Search($keywords)
{
     global $db;
     $jobs = array();
 
     $spchars = array(",",";",":");
     $keywords = str_replace($spchars," ", trim($keywords));
 
     $sql = 'SELECT a.id AS id
                    FROM jobs a, cities b
                    WHERE a.city_id = b.id
                    AND a.is_temp = 0 AND a.is_active = 1
                    AND MATCH (a.title, a.description, a.company, a.outside_location, b.name)
                    AGAINST ("'. $keywords .'" IN BOOLEAN MODE )';
        $result = $db->query($sql);
 
        while ($row = $result->fetch_assoc())
        {
            $current_job = new Job($row['id']);
            $jobs[] = $current_job->GetBasicInfo();
        }
        return $jobs;
    }

i would like to hear your feedback! post a comment whether or not this actually improved the search performance in your jobberBase website. do note that this is merely an experiment. you’ve been warned! 😉

8 Comments

Code School

  1. I just tried this now. Filled my jobs table with a few thousand records of test data and
    sorry to say but this does not work.

    When I type a 3 letter keyword no results are returned. When I type the full keyword, results are returned but the page freezes up and a warning is returned:

    “A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.

    Script: http://127.0.0.1/jobberbase/js/jquery.js:11

    I think the query is trying to return too much data. The results need to be split up into pages.

    Check out: http://www.php-mysql-tutorial.com/wikis

  2. hi, actually jobberBase has its own paging class. thanks for your feedback though! like i said this is an experiment, and not a solution (at least not yet) hopefully it gets explored more on the next version of jobberBase.

  3. about this 3-letter word, this is a mysql full-text searching feature and not because of how it was coded 😉

  4. Any luck using Sphinx as you suggested before?

  5. hi there, no luck yet. starting out with experimenting on full-text search first and maybe go up. we’ll see!

  6. is there anyway to make that in a new category like a new tab with a label advanced search (searching by city job type and etc..) Thanks Khaled

Leave a Response