2

What is the best way to rewrite this query:

SELECT myField 
FROM myPrefix.myTable 
WHERE myField2 IN (?) AND 
    GET_LOCK(CONCAT('action:',myField3), 0) = 1 
LIMIT 1

myField2 has an index on it.

Right now, MySQL evaluates the IN operation first (because it knows there is an index on it), and then pipelines the GET_LOCK operation.

How can I be sure that this will always be the case, and would not switch the other way around across MySQL upgrades/etc.

AKA, my question is, how can I make sure GET_LOCK is never evaluated first. I would like to keep this all in one query.

jn1kk
  • 5,012
  • 2
  • 45
  • 72
  • 1
    There are all sorts of sneaky ways to trick the optimizer into evaluating certain parts of queries before others (think: user variables), but I'm not certain you should be dabbling in this kind of black magic -- especially because this *is* subject to change across versions. What are you trying to achieve with your advisory locks? – fenway Aug 19 '13 at 17:42
  • I'm using locking so more than one server can connect to a DB and pick up a job. – jn1kk Aug 19 '13 at 17:43
  • Well, the `HAVING` clause is always evaluated after retrieving the matching resultset, so that seems like a good candidate. – fenway Aug 19 '13 at 17:46
  • Is there documentation for that? It makes sense conceptually, but I'd like to read the guarantee. – jn1kk Aug 19 '13 at 17:53
  • 1
    @fenway `The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)` I don't think that is a good solution (LIMIT is after HAVING, unless it gets applied to WHERE beforehand). – jn1kk Aug 19 '13 at 17:55
  • What happened when you simply re-ordered the where clause? – Dan Bracuk Aug 19 '13 at 18:13
  • Ordering doesn't matter, no.? – jn1kk Aug 19 '13 at 18:15
  • @fenway I doubt if 'HAVING' clause is the solution here, as 'HAVING' cluase has to be used with 'GROUP BY' which isn't necessary in this scenario right? – Renu Aug 19 '13 at 20:12
  • @Renu, MySQL allows `HAVING` for `SELECT`. Except the problem is, it gets performed before LIMIT. – jn1kk Aug 19 '13 at 20:21
  • @jsn: Even if the LIMIT applies after the HAVING clause, if you have no matching rows, it don't see why it should apply the LIMIT or the HAVING. I'll have to put some PoC code on an sql fiddle. – fenway Aug 23 '13 at 01:38
  • It is a problem since we would get, say 3 rows, it would apply HAVING with the lock, locking 3 rows, applying LIMIT 1, so those 2 other rows would get locked forever (until connection is reset that locked them). – jn1kk Aug 23 '13 at 18:27

1 Answers1

0

It depends on the kind of optimizer database is using.

Does order of where clauses matter in SQL

I am not sure below solution is optimized way but if you want 'GET_LOCK' to be executed only after checking 'myField2' then you can try this:

SELECT t.myField 
FROM (SELECT * FROM myPrefix.myTable  WHERE myField2 IN (?) ) t
WHERE GET_LOCK(CONCAT('action:',t.myField3), 0) = 1 
LIMIT 1
Community
  • 1
  • 1
Renu
  • 65
  • 3