# Maximum Insert Speed for mySQL through network?

**URL:** <https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932>\
**Category:** General\
**Created:** [30 August 2019 09:37 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932 "2019-08-30T09:37:02Z")\
**Posts on this page:** 14\
**Page:** 2

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [31 August 2019 19:23 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/22 "2019-08-31T19:23:08Z")

</div>

I have re-made my function block to insert 200 data in 1Hz. It works. Thx. However, I was using now(6) for mysql but then, I got 200 entries with the same time. Is there any easy way in node-red to create the datetime in this format?  
2019-09-01 02:26:34.779242 which is the same as now(6) in mySQL? Thx

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 September 2019 08:18 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/23 "2019-09-01T08:18:20Z")

</div>

I would try just using Date.now() and leaving it as a javascript date. Is suspect that the SQL node will convert that correctly.

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [1 September 2019 09:57 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/24 "2019-09-01T09:57:10Z")

</div>

> [@Colin](#):
>
> Date.now()

2019/9/1 下午5:53:40[node: MYSQL\_liftcar](http://192.168.9.104:1880/#)msg : error

"Error: ER\_TRUNCATED\_WRONG\_VALUE: Incorrect datetime value: '1567331622143' for column 'RealTime' at row 1"

I made some change by adding a timestamp node (repeat at 0.001s) + moment node to create a global value which the the time I need. However, when I use this global value to attach to my serial signal which comes at 0.005s, mySQL complained there is a duplicated entry of time...

[{"id":"e77fb21.e5d235","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"a1cdf489.9fa3f8","type":"inject","z":"e77fb21.e5d235","name":"","topic":"","payload":"","payloadType":"date","repeat":"0.001","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":800,"wires":[["a677ac13.f570e"]]},{"id":"a677ac13.f570e","type":"moment","z":"e77fb21.e5d235","name":"YYYY-MM-DD HH:mm:ss.SSSSSS","topic":"","input":"","inputType":"msg","inTz":"Asia/Hong\_Kong","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD HH:mm:ss.SSSSSS","locale":"zh\_TW","output":"payload","outputType":"msg","outTz":"Asia/Hong\_Kong","x":440,"y":800,"wires":[["4f431c1c.510e54"]]},{"id":"4f431c1c.510e54","type":"debug","z":"e77fb21.e5d235","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":680,"y":800,"wires":},{"id":"b5d9b59b.c78b28","type":"serial in","z":"e77fb21.e5d235","name":"","serial":"e2641fb4.38abf","x":90,"y":280,"wires":[["32b9d353.b10cac"]]},{"id":"32b9d353.b10cac","type":"function","z":"e77fb21.e5d235","name":"Add Date","func":"//var date;\n//date = new Date();\n//date = (date.getFullYear() + '-' + ('00' + (date.getMonth()+1)).slice(-2) + '-' + ('00' + date.getDate()).slice(-2) + ' ' + ('00' + date.getHours()).slice(-2) + ':' + ('00' + date.getMinutes()).slice(-2) + ':' + ('00' + date.getSeconds()).slice(-2));\nvar date = global.get("date3");\n\nvar newMsg = msg;\nnewMsg.payload = newMsg.payload.replace ('\n', '');\nnewMsg.payload = "("+newMsg.payload + "," + "'"+ date + "'" +")";\nreturn newMsg;","outputs":1,"noerr":0,"x":240,"y":280,"wires":[["7c1d5cd6.1807d4"]]},{"id":"7c1d5cd6.1807d4","type":"join","z":"e77fb21.e5d235","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","joinerType":"str","accumulate":false,"timeout":"","count":"200","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":280,"wires":[["f7e4e357.712b1"]]},{"id":"f7e4e357.712b1","type":"function","z":"e77fb21.e5d235","name":"Topic","func":"var newMsg = msg\nnewMsg.topic = "INSERT INTO liftcar\_table (Time\_Stamp, AcX, AcY, AcZ, AcX1, AcY1, AcZ1, IR, RealTime) VALUES " + msg.payload + ";";\nnewMsg.payload ="";\nreturn newMsg;","outputs":1,"noerr":0,"x":530,"y":280,"wires":[["505be3b7.f3d7fc","a6c77ed0.37fe4"]]},{"id":"505be3b7.f3d7fc","type":"mysql","z":"e77fb21.e5d235","mydb":"7850ebd4.b86ad4","name":"MYSQL\_liftcar","x":700,"y":280,"wires":[["7f9c3bb0.c734f4"]]},{"id":"7f9c3bb0.c734f4","type":"debug","z":"e77fb21.e5d235","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":890,"y":280,"wires":},{"id":"a6c77ed0.37fe4","type":"debug","z":"e77fb21.e5d235","name":"Frame from Sensors","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":720,"y":340,"wires":},{"id":"cf9bc84f.b42d08","type":"moment","z":"e77fb21.e5d235","name":"YYYY-MM-DD HH:mm:ss.SSSSSS","topic":"Set to Global Value","input":"","inputType":"msg","inTz":"Asia/Hong\_Kong","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD HH:mm:ss.SSSSSS","locale":"zh\_TW","output":"date3","outputType":"global","outTz":"Asia/Hong\_Kong","x":480,"y":140,"wires":[]},{"id":"311d4bc1.3d0324","type":"inject","z":"e77fb21.e5d235","name":"","topic":"","payload":"","payloadType":"date","repeat":"0.001","crontab":"","once":false,"onceDelay":0.1,"x":250,"y":140,"wires":[["cf9bc84f.b42d08"]]},{"id":"e2641fb4.38abf","type":"serial-port","z":"","serialport":"COM4","serialbaud":"115200","databits":"8","parity":"none","stopbits":"1","waitfor":"","newline":"\n","bin":"false","out":"char","addchar":"","responsetimeout":"10000"},{"id":"7850ebd4.b86ad4","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"lift\_database","tz":""}]

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [1 September 2019 09:59 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/25 "2019-09-01T09:59:22Z")

</div>

Export your flow again, edit your post and replace the current code, this time format it like:

```  
your flow code  
```

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [1 September 2019 11:07 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/26 "2019-09-01T11:07:48Z")

</div>

Do you know how to put this kind of date Time ms (2019-09-01 02:26:34.779242) into msg.payload? Thx

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 September 2019 11:17 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/27 "2019-09-01T11:17:38Z")

</div>

take a look at node-red-contrib-moment

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [1 September 2019 12:20 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/28 "2019-09-01T12:20:28Z")

</div>

Hi, I did use the moment. Input is 0.001s timestamp and output to a global variable. But the output time sometimes gives same value even through I call it every 0.005s. That's why I asked if I can put the Date with ms format into my code instead of going through this work-around process. Pls see my previous code for details. Thx

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [1 September 2019 12:33 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/29 "2019-09-01T12:33:51Z")

</div>

Change your code to something that can be used by the people here on the forum, as indicated above.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 September 2019 15:57 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/30 "2019-09-01T15:57:30Z")

</div>

> [@AK51](#):
>
> Hi, I did use the moment. Input is 0.001s timestamp and output to a global variable.

If you send it to a global variable then remember that the value will change every time you write to it, so may have changed by the time you get round to using it. Don't use a global variable, pass it in a message and add it into the sensor message then pass it on to MYSQL. Don't use global variables unless there is a really good reason.

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [2 September 2019 10:41 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/31 "2019-09-02T10:41:29Z")

</div>

Hi, but how to get the time in ms (like Date() but it has no ms \>\_\<) in the code instead of using node? Thx

「Colin via Node-RED Forum [nodered@discoursemail.com](mailto:nodered@discoursemail.com)」在 2019年9月2日 週一，上午12:07 寫道：

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 September 2019 11:07 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/32 "2019-09-02T11:07:18Z")

</div>

Construct a message with your data in message properties and put Date.now() in the payload. Pass that to the moment node and get it to put the formatted date in the payload, then modify the message to put the date in the appropriate place. The moment node will not change message properties that is not interested in.

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [2 September 2019 15:28 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/33 "2019-09-02T15:28:17Z")

</div>

It is working now. :\> Thx for all the help.  
var date;  
date = new Date();  
date = (date.getFullYear() + '-' + ('00' + (date.getMonth()+1)).slice(-2) + '-' + ('00' + date.getDate()).slice(-2) + ' ' + ('00' + date.getHours()).slice(-2) + ':' + ('00' + date.getMinutes()).slice(-2) + ':' + ('00' + date.getSeconds()).slice(-2) + '.' + ('00' + date.getMilliseconds()).slice(-6));

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [2 September 2019 16:53 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/34 "2019-09-02T16:53:08Z")

</div>

I haven't had time to work through this whole thread but are you using a prepared statement for your inserts? That should be a LOT faster if you aren't.

Also, I would think that batch inserts would be a lot more efficient?

---

<div class="post-metadata">

**Author:** ![ernestgwilsonii](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ernestgwilsonii/32/9387_2.png) [@ernestgwilsonii](https://discourse.nodered.org/u/ernestgwilsonii)\
**Post date:** [4 September 2019 13:51 UTC](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932/35 "2019-09-04T13:51:05Z")

</div>

If you are calling MySQL via a name (not by IP), put that name in your /etc/hosts to avoid DNS lookups or consider using an IP (not name) and turning off dns lookups like [http://slaptijack.com/software/mysql-disable-dns-lookups/](http://slaptijack.com/software/mysql-disable-dns-lookups/)

[Previous page](https://discourse.nodered.org/t/maximum-insert-speed-for-mysql-through-network/14932.md?page=1)