Custom Search Engine

Question

How would you implement a custom search engine?

What do you think about something like this:

SELECT *
FROM   jobs
WHERE  job_id IN (
        SELECT job_id
          FROM job_words
         WHERE word_id IN (SELECT word_id FROM words w WHERE text = 'carpenter'))
  AND  job_id IN (
        SELECT job_id
          FROM job_words
         WHERE word_id IN (SELECT word_id FROM words w WHERE text = 'buildings'))

or this:

SELECT j.*
      ,s.matches
  FROM jobs as j INNER JOIN
       (SELECT jw.job_id, count(*) as matches
          FROM job_words AS jw
               INNER JOIN (SELECT word_id FROM words w WHERE text IN ('carpenter', 'buildings')) AS w ON w.word_id = jw.word_id
        GROUP BY jw.job_id) as s ON s.job_id = j.job_id

Answer 1

You'd be better off building the tables from your keywords in advance. Your code is very inefficient. You're basically running O(n(n+n)) every time you run this code. Instead make the tables for all the 'carpenter' and 'buildings' before hand and if the table for the search query doesn't exist then use the code you posted.

Answer 2

SELECT * FROM jobs WHERE
    job_id in (SELECT job_id FROM job_words WHERE
        word_id in (SELECT word_id FROM words WHERE text in ('carpenter', 'buildings'))