We have created an index specifically for one query, but I'm finding that the query is taking 5 to 6 seconds to execute. I tried to get the unused index using the following query and I noticed that the index is listed in the unused index list. Please suggest how to get better performance for the following query.
Query where clause: WHERE parsedjobdescription IS NOT NULL AND is_updated != 0
Index: KEYidx_jobs_feed_parsedjobdescription_is_updated(parsedjobdescription(700),is_updated)
Unused indexes: SELECT * FROM sys.schema_unused_indexes;
Column: parsedjobdescription varchar(50000) DEFAULT NULL is_updated tinyint(1) DEFAULT '0'
Explain query:
Possible keys: idx_jobs_feed_parsedjobdescription_is_updated, idx_is_updated
Key: idx_jobs_feed_parsedjobdescription_is_updated
Key length: 703
Number of lines: 1
Filter: 50.0
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Three things to avoid using two columns of an index:
IS NOT NULL - Once the range is reached, the remaining columns will not be used.
is_updated != 0 -- This is also a "range".
There is a problem with the index prefix.
If the test is
is_updated = 1, you can flip the index (or add another index):