0

In my company while I was looking into code, I came across this query:

update people set approval="A" where peopleid in (peoples);

Here peoples is a string containing 1,2,3,4,5,6 like this. Sometimes it contains more than 150 names. I thought of doing a batch update. But my other team members say it executes only one time and improves. But I have doubt that MySQL internally executes this 150 times. I see it taking a lot of time. I searched in net but did not find an answer. Can anybody tell me here that query is fine or batch update is good?

The One and Only ChemistryBlob
  • 7,864
  • 10
  • 36
  • 69
murali n
  • 13
  • 2
  • [Turn on the Radar](http://stackoverflow.com/a/38394479) and find out – Drew Aug 01 '16 at 16:33
  • The SQL will "execute" as many times as the SQL statements told to run are told to run. There may be differing ideas of what "execute" means, but such *require an actual query/queries* to answer in a productive manner. (The Query Planner can do whatever it wants with the SQL, as long as it results in the correct result - *how* it "executes" depends on factors such as the indices available.) – user2864740 Aug 01 '16 at 16:35
  • That is: In the case hinted at, a *single* SQL DML *statement* only "executes" once, regardless of the actual operations taken by the Query Planner. The question is still *how* it "executes"; and, possibly, how it may affect the overall application performance. – user2864740 Aug 01 '16 at 16:41
  • With your initial request the more you will have ids the longer will be the parsing time and the in clause should be slower to execute too so knowing that with one request you won't save much in term of I/O compare to a batch update, I believe that batch update should be more scalable but it is a blind assumption only real tests are valid answers. – Nicolas Filotto Aug 01 '16 at 16:42
  • If its taking lot of time to execute check if that column is indexed. If you index it will get executed faster – Rohan S Raju Aug 01 '16 at 16:52
  • Also, the `IN` clause is very expensive. It would be more efficient to change it for 150 `OR` clauses. Would be a pain for the eyes, but performance would be improved. – antorqs Aug 01 '16 at 17:24
  • @aquiros - are you sure? According to this post, OR is slower than IN: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – Galz Aug 01 '16 at 17:30

0 Answers0