CODEWITHSUNDEEP
mysqlsqldatabasesubstringsubstr
ojal
ojal

Reputation: 15

How to optimize query with SUBSTR

SELECT
    XYZ_ID
FROM XYZ
WHERE
    SUBSTR(Processor_Key, 24, 5) = '01000' AND
    SUBSTR(Processor_Key, 32, 5) = '00900' AND
    SUBSTR(Processor_Key, 40, 5) = '00070' AND
    SUBSTR(Processor_Key, 48, 5) = '10000' AND
    SUBSTR(Processor_Key, 56, 5) = '66000' AND
    SUBSTR(Processor_Key, 64, 5) = '09000' AND
    SUBSTR(Processor_Key, 72, 5) = '00050';

How to optimize the query and reduce the cost of query?

Upvotes: 0

Views: 836

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

High risk of being downvoted, but one thing which might work would be to replace your many SUBSTR calls with a single LIKE expression:

SELECT XYZ_ID
FROM XYZ
WHERE
    Processor_Key LIKE '_______________________01000___00900___00070___10000___66000___09000___00050%';

The reason this might outperform your original query is that the LIKE expression can be evaluated by making at most one single pass across the first 77 characters of the processor key. In your original query you make seven distinct calls to SUBSTR each of which might have to be evaluated in order to make a decision.

This being said, as @Gordon and the above comments have pointed out, the best long term solution might be to break up your key into separate columns and index each one. This would let you take advantage of the full power of a database, e.g. via indexing.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can't. The SQL engine cannot use a traditional index for this query.

What are your options? Depending on what the string really looks like, you might be able to use a full text search.

Alternatively, you might want to pull the codes out into separate columns -- and then build indexes on those columns. Presumably, the codes actually mean something. You can do this explicitly on insert or use a trigger to populate the columns.

Another alternative is available in the most recent versions of MySQL, you can create a generated column for each substring, and then create an index on them.

Upvotes: 1

Related Questions