Unable to limit

Question

I have the following query:

UPDATE pc
INNER JOIN cams 
   ON cams.cam_id = pc.camuid 
SET 
    timestamp = NOW(),
    uid = @out_param:=uid
WHERE zone = 1 
  AND (unable = '0' OR unable IS NULL)
  AND (corrected_plate = ''  OR corrected_plate IS NULL)
  AND (timestamp IS NULL OR timestamp < (NOW() - INTERVAL @interval MINUTE)) 
LIMIT 1; 
SELECT @out_param;

I am unable to run that query as I have a LIMITclause in a join query. I cannot figure out how to spread it out into a subquery while maintaining performance. I need this query to be as fast as possible, and the optimiser of MySQL has not provided much help so far.

This is the error obtained from MySQL, as the above is not allowed: Incorrect usage of UPDATE and LIMIT

This is the error obtained from MySQL, as the above is not allowed: `Incorrect usage of UPDATE and LIMIT` — MathLover, Dec 05 '17 at 14:32
You are using `LIMIT` without an `ORDER BY` clause. So from the pc records matching your criteria you are trying to pick one arbitrarily and update it? — Thorsten Kettner, Dec 05 '17 at 14:34
You are probably asking the wrong question [**What is the XY problem?**](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) You should tell us what you want to do so we can help you. Right now you are trying to do something is just wrong. — Juan Carlos Oropeza, Dec 05 '17 at 14:41
@JuanCarlosOropeza - What I am trying to do is pick a row and set the `timestamp` on this particular row, and return the `uid` of that respective row. The row is selected based on a number of conditions spanning fields in another table — MathLover, Dec 05 '17 at 14:43
Any random row matching the conditions? do `PC` has any primary key ? — Juan Carlos Oropeza, Dec 05 '17 at 14:45
@Hackerman - ORDER BY and LIMIT cannot be used in an UPDATE statement with a join. Hence my problem — MathLover, Dec 05 '17 at 14:46
@JuanCarlosOropeza - The PK is `uid`. I can also just choose the row with the smallest uid, so it does not have to be arbitrary. — MathLover, Dec 05 '17 at 14:46
You need to store the result of `select cam_id from pc ORDER BY RAND() LIMIT 1`, so there you have your random candidate and in your update instead of limit 1 you just add an extra condition in your where statement. — Hackerman, Dec 05 '17 at 14:48

Juan Carlos Oropeza · Accepted Answer · 2017-12-05T15:07:50.407

3

Just use a subquery to decide which uid update.

UPDATE PC
SET timestamp = NOW(),
   uid = @out_param:=uid
WHERE uid = (SELECT uid
             FROM ( SELECT * FROM pc ) as pc2
             INNER JOIN cams 
                ON cams.cam_id = pc2.camuid 
             WHERE zone = 1 
               AND (unable = '0' OR unable IS NULL)
               AND (corrected_plate = ''  OR corrected_plate IS NULL)
               AND (timestamp IS NULL OR timestamp < (NOW() - INTERVAL @interval MINUTE)) 
             ORDER BY uid -- optional
             LIMIT 1)

edited Dec 05 '17 at 15:07

answered Dec 05 '17 at 14:48

Juan Carlos Oropeza

47,252
12
78
118

Error Code: 1093. You can't specify target table 'pc' for update in FROM clause – MathLover Dec 05 '17 at 15:03
1

Typical MySQL issue. Try replacing `FROM pc as pc2` by `FROM (select * from pc) as pc2`. Looks ridiculous, but is usually the way to circumvent this problem. – Thorsten Kettner Dec 05 '17 at 15:07
Wouldn't that slow it down even more though? – MathLover Dec 05 '17 at 15:09
Query is still running (been around 5 minutes so far). The solution I'm after has to execute in milliseconds, which I can achieve without the limit part (but I need to only return 1 row) – MathLover Dec 05 '17 at 15:15
You probably havent set the proper index yet. If you run only the subquery `SELECT uid ...` how long take? – Juan Carlos Oropeza Dec 05 '17 at 15:22
Unfortunately the table `pc` has around 10 million records, so a query in that format is going to take long for sure. – MathLover Dec 05 '17 at 15:25
I have table with 100 millions rows and query work ok, you need create the proper index but that is a different question. Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**MySQL**](http://dba.stackexchange.com/questions/15371/how-do-i-get-the-execution-plan-for-a-view) and [**How do I obtain a Query Execution Plan?**](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) – Juan Carlos Oropeza Dec 05 '17 at 15:27
@JuanCarlosOropeza - Without the subquery and without the limit 1 part, it takes 0.043 seconds to execute. With the query as it is above, it takes around 20 minutes. – MathLover Dec 05 '17 at 15:30
I told you to test the subquery. Check the index and execution plan. Show us what index have you created. But again that is a different question. I already solve your `LIMIT` question so just create a new one so we can handle the perfomance issue. But be ready to include the explain plan – Juan Carlos Oropeza Dec 05 '17 at 15:33
Accepted as it might solve someone else's problem. In my case the performance was too slow, even though I optimised – MathLover Dec 07 '17 at 08:54
I told you. We solve performance question every day. But we need the explain plan to know where to improve. Just ask another question and provide the relevant information. – Juan Carlos Oropeza Dec 07 '17 at 13:08

Unable to limit

1 Answers1