SQL Server: one table fulltext index vs multiple table fulltext index

Question

I have a search procedure which has to search in five tables for the same string. I wonder which is better regarding read performance?

To have all tables combined in one table, then add a full text index on it
To create full text indexes in all those tables and issue a query on all of them, then to combine the results

How big the 5 tables are? Please provide the table schema!! Fulltext index are use to search large string and documents!! — Hiten004, Feb 09 '16 at 19:15
I have 5 tables which contain roughly 500k of rows for each table. In this case I was wondering which is better regarding query performance. consolidating all those tables in one table and creating one fulltext to query those data? or creating a fulltext index and query those indexes when searching for data? maybe i need to test each approach — rahebirizah, Feb 13 '16 at 16:38

score 4 · Answer 1 · edited Feb 09 '16 at 21:08

I think something to consider when working with performance, is that reading data is almost always faster than reading data, moving data and then reading again rather than just reading once.

So from your question if you are combining all the tables into a single say temporary table or table variable this will most definitely be slow because it has to query all that data and move it (depending on how much data you are working with this may or may not make much of a difference). As well regarding your table structure, indexing on strings only really becomes efficient when the same string shows up a number of times throughout the tables.

I.e. if you are searching on months (Jan, Feb, Mar, etc...) an index will be great because it can only be 1 of 12 options, and the more times a value is repeated/the less options there are to choose from the better an index is. Where if you are searching on say user entered values ("I am writing my life story....") and you are searching on a word inside that string, an index may not make any difference.

So assuming your query is searching on, in my example months, then you could do something like this:

SELECT value
FROM (
      SELECT value FROM table1
      UNION
      SELECT value FROM table2
      UNION
      SELECT value FROM table3
      UNION
      SELECT value FROM table4
      UNION
      SELECT value FROM table5
) t
WHERE t.value = 'Jan'

This will combine your results into a single result set without moving data around. As well the interpreter will be able to find the most efficient way to query each table using the provided index on each table.

SQL Server: one table fulltext index vs multiple table fulltext index

1 Answers1