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 - please tell me about exists in sql
- [sql server/vscode/ms-sql] about creating schema and adding tables
- [microsoft sql server/pymssql] connection to the db fails
- [python] about aoj-0105 book index
- web server - about the name when acquiring the domain server for hp production
- sql server - i want to know the behavior of null check in the where clause
- i want to connect to sql server with access
- sql server - i don't know how to prevent sql injection attacks
- about building failover clustering in windows server 2019
- php - about launching the server of codeigniter (mac)
- sql server - i have installed adobe creative cloud, but i am unable to install it with an error
- sql server - i want to successfully use the session id in implementing login authentication
- sql server - i want to know the whole flow when writing sql
- about api server framework selection
- sql server - i want to cancel the mirroring that consists of two db servers and use only one
- sql server - i can no longer log in to wordpress being built on my local server
- i want to convert the 4-digit number stored in sql server to the time format (hh:mm) and save it (since it is converted to time,
- about api server nginx
- this is a question about changing the table format of standard sql
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- python - you may need to restart the kernel to use updated packages error
- python 3x - typeerror: 'method' object is not subscriptable
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - unresolved reference comes out in kotlin