Search
Search

Improve performance of a join between a varchar field and a substring applied to another field

debugcn Published at Dev
6
kiks73

I have a MySQL varchar field with values like these:

'00007_1'
'00016_1'
'00016_2'
'00231_1'
'00326_3'

I'm looking for the best way to put this field in join with another table with a varchar field with values like these (I list those that match previous):

'7'
'16'
'16'
'231'
'326'

This is how I've made:

SELECT *
FROM   R
INNER JOIN RC ON Trim(leading '0' from Substring(R.N_DEN,1,instr(R.N_DEN,'_')-1)) = RC.N_DEN

Anybody knows if there is a quicker and better performace solution?

rogue-one

Create a derived column with values pre-derived in the joining table. However there will be a space trade-off.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Improve SORTING performance on long VARCHAR field

From Dev

Join based on part of a varchar field matching another field

From Dev

SQL Regex - Replace with substring from another field

From Dev

Remove a substring in a varchar field from multiple rows of a table

From Dev

How to improve query performance with auto increment field as primary key

From Dev

Join Fields if another field equals something specific

From Dev

Mysql 'between' condition not working if value is greater than 9999 with varchar field

From Dev

convert varchar to datetime field

From Dev

Group characters of varchar field

From Dev

Can you compare varchar field one of table and char field of another table

From Java

MySQL Sorting by substring of a field

From Dev

Order by Field with two substring

From Dev

Does adding an explicit column of SHA-256 hash for a CLOB field improve the search(exact match) performance on that CLOB field

From Dev

how to convert a varchar field value into an integer field

From Dev

how to convert a varchar field value into an integer field

From Dev

time difference betweern varchar field and timestamp field

From Dev

How can I improve performance of Field.set (perhap using MethodHandles)?

From Dev

Solr: How can I improve the performance of a filter query (for a specific value, not a range query) on a numeric field?

From Dev

Solr: How can I improve the performance of a filter query (for a specific value, not a range query) on a numeric field?

From Dev

Mysql unique index over 2 fields, is a separate index required for a single field of that set to improve performance?

From Dev

postgres text field performance

From Dev

MySQL join two table with the maximum value on another field

From Dev

R data.table doing an inner join on a field and operating on another?

From Dev

How to select field in another table join by eager loading in Laravel

From Dev

How to join a table with two tables by choosing another field value

From Dev

SQL - Join two fields in the same table to a single field in another table

From Dev

How to select field in another table join by eager loading in Laravel

From Dev

MongoDB Shell: Join a single field from another collection

From Dev

Improve results of a "search" input field?

Related Related

  1. 1

    Improve SORTING performance on long VARCHAR field

  2. 2

    Join based on part of a varchar field matching another field

  3. 3

    SQL Regex - Replace with substring from another field

  4. 4

    Remove a substring in a varchar field from multiple rows of a table

  5. 5

    How to improve query performance with auto increment field as primary key

  6. 6

    Join Fields if another field equals something specific

  7. 7

    Mysql 'between' condition not working if value is greater than 9999 with varchar field

  8. 8

    convert varchar to datetime field

  9. 9

    Group characters of varchar field

  10. 10

    Can you compare varchar field one of table and char field of another table

  11. 11

    MySQL Sorting by substring of a field

  12. 12

    Order by Field with two substring

  13. 13

    Does adding an explicit column of SHA-256 hash for a CLOB field improve the search(exact match) performance on that CLOB field

  14. 14

    how to convert a varchar field value into an integer field

  15. 15

    how to convert a varchar field value into an integer field

  16. 16

    time difference betweern varchar field and timestamp field

  17. 17

    How can I improve performance of Field.set (perhap using MethodHandles)?

  18. 18

    Solr: How can I improve the performance of a filter query (for a specific value, not a range query) on a numeric field?

  19. 19

    Solr: How can I improve the performance of a filter query (for a specific value, not a range query) on a numeric field?

  20. 20

    Mysql unique index over 2 fields, is a separate index required for a single field of that set to improve performance?

  21. 21

    postgres text field performance

  22. 22

    MySQL join two table with the maximum value on another field

  23. 23

    R data.table doing an inner join on a field and operating on another?

  24. 24

    How to select field in another table join by eager loading in Laravel

  25. 25

    How to join a table with two tables by choosing another field value

  26. 26

    SQL - Join two fields in the same table to a single field in another table

  27. 27

    How to select field in another table join by eager loading in Laravel

  28. 28

    MongoDB Shell: Join a single field from another collection

  29. 29

    Improve results of a "search" input field?

HotTag

Archive