1

I have got three tables that are supposed to be joined, the query looks like this:

select distinct a.job 
from a
join  b on  a.job=b.id 
join  c on  c.id =a.path  
where c.path like '//depot/a/b/c/d/MARSR_CL286896_297899/%';

This query will always timeout. However, if I change the path comparison to some condition else, the query just run perfectly, for example:

 select distinct a.job 
 from a
 join  b on  a.job=b.id 
 join  c on  c.id =a.path  
 where c.path like '//depot/a/b/c/d/%';

Considering the difference of the path parameter, will the numbers and underscores the culprit that slows the statement? I have created index for the 'path' field

result of 'explain' command

1   SIMPLE  b   index   PRIMARY       job      62           73580   Using index; Using temporary
1   SIMPLE  a   ref     path,job      job       8   b.id    153 
1   SIMPLE  c   eq_ref  PRIMARY,path  PRIMARY   8   a.path  1   Using where
Community
  • 1
  • 1
Chen Xie
  • 3,849
  • 8
  • 27
  • 46

1 Answers1

3

Yes. An underscore is a wildcard character too, which matches any (single) character, so this makes the query quite a lot more complex, since it can only use the index on the first part, until the first underscore, and needs to scan all matches on that part with a relatively complex regular expression to match the rest, while otherwise it could do a simple 'starts with'.

You should escape it to treat it as a normal underscore. You can escape it by adding a \ in front of the underscores. You can also escape a percent sign like this, using \% if you would need to.

select distinct a.job 
from a
join  b on  a.job=b.id 
join  c on  c.id =a.path  
where c.path like '//depot/a/b/c/d/MARSR\_CL286896\_297899/%';
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • The % looks like an actual wildcard. – Andreas Wederbrand Oct 18 '12 at 19:52
  • I know. Just saying you could escape it if needed. As you can see, I didn't in the code. There's just the forward slash of the path. – GolezTrol Oct 18 '12 at 19:55
  • @AndreasWederbrand yep the '%' is a true wildcard intended to use – Chen Xie Oct 18 '12 at 19:57
  • @GolezTrol that also explains why even it sometimes does not timeout but returns more results than expected. Thanks for your answer, i appreciate it – Chen Xie Oct 18 '12 at 19:58
  • By the way, is that only when 'like' is used then the '_' will be interpreted as a wildcard character? – Chen Xie Oct 19 '12 at 22:09
  • Yes, these wildcard only work with `LIKE`, but you also got MATCH AGAINST, which uses its own set of wildcards, and you can use regular expressions in some cases, but the normal `=`, `!=` and such don't use wildcards at all and interpret `_` and `%` as normal characters. – GolezTrol Oct 19 '12 at 22:19