I use sqlserver.
A full text index is prepared and searched.
There are currently about 100 million records.
I'm currently testing, but if I search for keywords with many records,
It takes about 10 seconds.
If i search with keywords with few records, there is an immediate response.
The SQL being executed is as follows.
select * from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"') ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
If the target record matches the above SQL, 20 images are considered to be missing.
If there are a large number of cases, we have no idea why the response is slow.
Windows Server 2016
SQL Server 2017
CPU E5-2650 * 2
DB size 120GB
Answer # 1
Is there an index for
Is desc specified?
select uses only one index, so that index must contain an item with where or order by.
Let's nest the indexes to be used separately.
[identity_column_name] If there is a DESC index, isn't it improved by the following query?
select * from [TABLE_NAME] where primary key in ( select primary key from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"') ) ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
However, the above does not make sense if the following SQL is not fast in the first place.
select * from [TABLE_NAME] ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
select primary key from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')
Since the second is slow, it may be useless if you do not think of an approach that devise conditions so that search results are reduced.
select * from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"') OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
select count (*) from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')
Etc. (the latter may be slow), but when the threshold is exceeded, a message is issued to add a condition.
- [sql server/vscode/ms-sql] about creating schema and adding tables
- server - about netlify settings in muumuu domain
- sql - i can't connect to the db with sakura's rental server
- there is a function that outputs a text file from sql server with the integration service package (dtsx), but it fails when the
- sql server - active node and passive node what does signing in to the active node mean?
- sql server - download forms using ssrs from the aspnet (c #) screen
- sql server reporting services - about the parameters of the reporting service
- server - about the release of the site renewal
- sql server - i want to get the number of value matches between multiple columns in an sql statement
- sql server - sqlserver 2019 (express) half-width/full-width conversion
- c # - what to do about the error message "index is out of array bounds"
- java - about sql: (colon)
- [sql server] is the database generated from the script the same as the database from which it was generated?
- sql server - sql i want to extract data at a fixed time across days
- sql - i want to find out what is causing the server to be overloaded from the query when the load is high
- sql server - i want to restore with the sql command
- sql server - extract commands to execute gui operations in sqlserve management studio
- python - about "could not retrieve index file" error
- sql server - [aws] i want to start unicorn on an ec2 server
- web server - i want to know about the wireless lan configuration