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.
