StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhow do I speed up substr searches in my query
text
Body
copied!<p>I am using this as a sub select (in a simple query joining 2 other tables) and as you can imagine it takes a while to run. going on 6 hours+ so far. is this the only way to do this? I know that doing another JOIN instead of a sub select might help a bit. but the main bottleneck is all these OR's and sub-strings. </p> <pre><code>SELECT ex_array FROM service_x WHERE ( substr(ex_array,1,2) = 'FW' OR substr(ex_array,3,2) = 'FW' OR substr(ex_array,5,2) = 'FW' OR substr(ex_array,7,2) = 'FW' OR substr(ex_array,9,2) = 'FW' OR substr(ex_array,11,2) = 'FW' ) OR ( substr(ex_array,1,2) = 'IL' OR substr(ex_array,3,2) = 'IL' OR substr(ex_array,5,2) = 'IL' OR substr(ex_array,7,2) = 'IL' OR substr(ex_array,9,2) = 'IL' OR substr(ex_array,11,2) = 'IL' ) OR ( substr(ex_array,1,2) = 'IN' OR substr(ex_array,3,2) = 'IN' OR substr(ex_array,5,2) = 'IN' OR substr(ex_array,7,2) = 'IN' OR substr(ex_array,9,2) = 'IN' OR substr(ex_array,11,2) = 'IN' ) OR ( substr(ex_array,1,2) = 'IK' OR substr(ex_array,3,2) = 'IK' OR substr(ex_array,5,2) = 'IK' OR substr(ex_array,7,2) = 'IK' OR substr(ex_array,9,2) = 'IK' OR substr(ex_array,11,2) = 'IK' ) OR ( substr(ex_array,1,2) = 'IH' OR substr(ex_array,3,2) = 'IH' OR substr(ex_array,5,2) = 'IH' OR substr(ex_array,7,2) = 'IH' OR substr(ex_array,9,2) = 'IH' OR substr(ex_array,11,2) = 'IH' ) OR ( substr(ex_array,1,2) = 'KP' OR substr(ex_array,3,2) = 'KP' OR substr(ex_array,5,2) = 'KP' OR substr(ex_array,7,2) = 'KP' OR substr(ex_array,9,2) = 'KP' OR substr(ex_array,11,2) = 'KP' ) ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload