Sql-server – Better way to full text query across multiple tables

full-text-searchperformancequery-performancesql server

I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow:

SELECT t0.[Key], t0.[Nr] 
FROM dbo.[Customers_View] AS t0
 WHERE (t0.[Deleted] IS NULL)
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t1 WHERE t0.[Key] = t1.[Key] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t2 WHERE t0.[Key] = t2.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t3 WHERE t0.[Key] = t3.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t4 WHERE t0.[Key] = t4.[CustomerKey] AND CONTAINS(*, '"smith"')))
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t5 WHERE t0.[Key] = t5.[Key] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t6 WHERE t0.[Key] = t6.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t7 WHERE t0.[Key] = t7.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t8 WHERE t0.[Key] = t8.[CustomerKey] AND CONTAINS(*, '"munich"')))

Is there a good way to speed up this Kind of query? Usually it is a bit more complex, I simplyfied it to the full text part.

Maybe there is a better way to to fulltext queries across multiple tables?

Best Answer

I do not know how dynamic your queries are, but naturally each table needs a separate query for that table.

Assuming that you are constantly querying this 4 table group, it might be worthwhile to create an indexed view that included the searchable fields you need from the 4 tables into a single view.

This means the overhead of maintaining the indexed view, but may help in this case.

Of course, if you have a widely varying array of tables that might be joined, you would need to consider whether this is a good idea for you or whether you should query each table separately as you are doing.

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.

Sql-server – Full-text search in SQL Server on multiple tables fails

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

Best Answer

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

Sql-server – Full-text search in SQL Server on multiple tables fails

Related Question