Using sql server 2012. I have a single table with about 12 million rows in it. I have a full text index on the table on a few of the Text fields. My primary key is DocumentID.
My query to the table is as follows:
SELECT COUNT(Document.DocumentID) OVER ( ) AS TotalRecordsReturned ,
WHERE CONTAINS ( Document.Text4, '"north*"' )
ORDER BY Document.Text1 ASC OFFSET ( 1-1 ) * 25 ROWS FETCH NEXT 25 ROWS ONLY;
I'm using Paging (OFFSET syntax), FULL TEXT Wildcard , and OVER in order to get total records returned.
The above query returns 25 records in about 30 seconds, telling me that there are 821,551 matching records.
If I run a more specific query (ie.. wildcard text of "154 North Blvd"), I can return 200 rows almost immediation from the 12 million rows. So, clearly the full text indexing is working well when I have a more specific query but it is all over the board from 15 seconds up to 1 minute 30 seconds when the number of overall matching records is high -- even though I only need to return 25 rows at a time. I haven't figured out how else to optimize this query when searching for a broader search term. Am I expecting too much from this query?
Is there anything else you need to help to optimize this query?