Negated Search Terms and Search Modifiers

After yesterday’s post about slow negative search terms and MySQL’s disregard for the EXCEPT operator, I came upon a decent solution for EveKnows.com’s problem. With some (slightly) clever use of LEFT JOINs, I was able to cut the running time of queries with a single negated term in half, and that run time drops by an order of magnitude for queries involving multiple negated terms. W00t! The trick was to build a temporary table of gallery IDs which contain the negated terms, then take the LEFT JOIN of galleries matching the desired terms with the temporary table. This gives us a resulting table with two columns, matches.gallery_id and neg_matches.gallery_id; any rows with a non-NULL value for neg_matches.gallery_id are then dropped, resulting in the proper set of matches. A fairly simple solution; I feel pretty dumb for not seeing it earlier.

While I was working on this, I noticed that the existing src: and site: query modifiers were not functiong properly. Due to the new SQL database schema, a quick fix isn’t possible. I’ve dropped these modifiers for the time being, but intend to support them both at some point in the future.

There are no comments on this post

Leave a Reply

You must be logged in to post a comment.