StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As you've pointed out, this is pretty much impossible in the manner in which you're hoping. Depending on the larger context of your situation, you have some options. In fact, that article gives you some help.</p> <p>Here are some ideas that come to mind when I read that article:</p> <ol> <li>Embed the filter criteria in your text <ul> <li>Check out the "Consider embedding filter conditions as keywords in the indexed text" bullet in the article.</li> <li>This essentially says you could consider putting your date as an easily-locatable string in the text. E.g., "<DateAdded:YYYYMMDD>" Something you can strip out before processing the text.</li> <li>Probably not great for ranges, though the workaround for this is a coarser grained filter term. I.e., instead of DateAdded:YYYYMMDD, use WeekAdded:YYYYWW, then you're pulling 7-14 days' worth off FTS and your <code>added_date</code> predicate can further narrow it.</li> <li>Probably stops being practical well before 100 search terms.</li> <li>Adding a filter criteria means running an update to add it to all 7m+ records</li> <li>Otherwise, this seems closest in spirit to what you are after</li> </ul></li> <li>Two tables - horizontal partition <ul> <li>If you chiefly look back only a few days, you can try simply keeping a second table with FTS with only the last <em>n</em> days of records.</li> <li>May be a PITA to maintain.</li> </ul></li> <li>Two tables - vertical partition <ul> <li>Split your table into 1 with the values you're going to filter by in SQL and another with the FTS text. Then use CONTAINSTABLE to bring them together.</li> <li>You are still doing 2 table hits. The one benefit, though, is that your reduced table will be tighter, with more records per page, allowing less IO.</li> <li>Admittedly, this improvement might not even be noticeable. And for all that a covering index might be as good.</li> </ul></li> <li>Live with it <ul> <li>Do you have the performance numbers to show that this double-read is resulting in a big loss in performance? Assuming your PK is < 10 bytes and you have an index on your searched fields, I'd guess you'd need 20k+ filtered records returned to notice the extra read?</li> </ul></li> </ol> <p>Do any of these work for your particular situation?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload