1

I can't really use an IN statement because the wildcards in my situation are very necessary. Any help is appreciated.

AND ((NOT (j.Note LIKE '%audit%'
            OR j.Note LIKE '%discrepancy%'
            OR j.Note LIKE '%returns%'
            OR j.Note LIKE '%shipment error%'
            OR j.Note LIKE '%inventory adjustment%'))
            OR j.Note IS NULL)
Justin Emmons
  • 55
  • 6
  • `REGEXP` maybe? Not sure it would actually be any better though. – Uueerdo May 15 '15 at 17:38
  • Can you improve your data structure? Your query becomes much simpler if you have separate columns to track whether a given row is an audit, discrepancy, return, etc. Tinyint columns with values of 1 for true and 0 for false should suffice. – George Cummins May 15 '15 at 18:19
  • @GeorgeCummins This is probably the best answer, but I am still curious if there is another way if we assume I can't modify the data structure. – Justin Emmons May 15 '15 at 18:54
  • Check this out: http://stackoverflow.com/questions/4172195/mysql-like-multiple-values – Nagasimha Iyengar May 15 '15 at 21:01
  • @NagasimhaIyengar That's the same as my current query. The problem with it is that it doesn't perform very well. – Justin Emmons May 15 '15 at 21:16
  • @Uueerdo `REGEXP` prevents indexes from being used, and in my situation the index is the only thing keeping this query under a minute. – Justin Emmons May 15 '15 at 22:20
  • @JustinEmmons I am fairly sure using `LIKE` with a leading `%` prevents index use as well. – Uueerdo May 15 '15 at 22:27

1 Answers1

0

You are stuck with looking at all rows.

  • LIKE '%...' cannot use an index because of the leading wildcard
  • REGEXP can do all the testing in one try, versus multiple LIKEs, so it might be faster
  • AND NOT (...) does not help
  • OR does not help
  • FULLTEXT can't be very helpful because of the 'negative' testing.
  • etc.

I recommend this:

AND j.Note IS NOT NULL
AND j.Note REGEXP 'audit|discrepancy|returns|shipment error|inventory adjustment'

If Note has lots of NULLs, then INDEX(Note) may help by avoiding the NULLs.

Here's another approach: As you insert rows, categorize them as "audit" or ... Then have another column (perhaps a SET) that flags which categories each Note belongs to.

If each Note belongs to only one category, then use ENUM and index it. If not, testing the SET would be a little faster than LIKEs or REGEXP.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I would select this as the best answer, but in my example `j.Note` can be `NULL` and `AND j.Note NOT LIKE 'audit|discrepancy|returns|shipment error|inventory adjustment'` so maybe it would be: `AND j.Note NOT REGEXP 'audit|discrepancy|returns|shipment error|inventory adjustment' OR j.Note IS NULL` – Justin Emmons May 19 '15 at 17:44
  • Be cautious of `a AND b OR c` -- That means `(a AND b) OR c`, _not_ `a AND (b OR c)`. – Rick James Jun 04 '15 at 23:04