Index available but still not used - MySQL

Question

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

Answer 1

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.

  INDEX(parsedjobdescription(700), ...  -- won't get past that prefix to use anything after it.

If the test is is_updated = 1, you can flip the index (or add another index):

INDEX(is_updated, parsedjobdescription(100))