{"id":26700,"date":"2022-12-19T12:30:55","date_gmt":"2022-12-19T07:00:55","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/"},"modified":"2022-12-19T12:30:55","modified_gmt":"2022-12-19T07:00:55","slug":"solved-which-insert-query-run-faster-and-accurate","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/","title":{"rendered":"[Solved] Which Insert query run faster and accurate?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-27920609\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"27920609\" data-parentid=\"27920490\" data-score=\"5\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<h1>TL;TR<\/h1>\n<p>The second query will be faster. Why? Read below&#8230;<\/p>\n<p>Basically, a query is executed in various steps:<\/p>\n<ul>\n<li>Connecting: Both versions of your code have to do this<\/li>\n<li>Sending query to server: Applies to both versions, only the second version sends only one query<\/li>\n<li>Parsing query: Same as above, both versions need the queries to be parsed, the second version needs only 1 query to be parsed, though<\/li>\n<li>Inserting row: Same in both cases<\/li>\n<li>Inserting indexes: Again, same in both cases <em>in theory<\/em>. I&#8217;d expect MySQL to build update the index after the bulk insert in the second case, making it <em>potentially<\/em> faster.<\/li>\n<li>Closing: Same in both cases<\/li>\n<\/ul>\n<p>Of course, this doesn&#8217;t tell the whole story: Table locks have an impact on performance, the MySQL config, use of prepared statements and transactions might result in better (or worse) performance, too. And of course, the way your DB server is set up makes a difference, too.<br \/>\nSo we return to the age-old mantra:<\/p>\n<h1>When in doubt: test!<\/h1>\n<p>Depending on what your tests tell you, you might want to change some configuration, and test again until you find the best config.<\/p>\n<p>In case of a big data-set, the ideal compromise will probably be a combination of both versions:<\/p>\n<pre><code>LOCK TABLE paper WRITE\n\/* chunked insert, with lock, probably add transaction here, too *\/\nINSERT INTO paper VALUES ('a', 'z'), ('b','c');\nINSERT INTO paper VALUES ('a', 'z'), ('b','c');\nUNLOCK TABLES;\n<\/code><\/pre>\n<hr>\n<p>Just RTM &#8211; <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/insert-speed.html\">MySQL insert speed<\/a>:<\/p>\n<blockquote>\n<p>If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.4, \u201cServer System Variables\u201d.<\/p>\n<\/blockquote>\n<p>If you can&#8217;t use multiple values, then locking is an easy way to speed up the inserts too, as explained on the same page:<\/p>\n<blockquote>\n<p>To speed up INSERT operations that are performed with multiple statements for nontransactional tables, lock your tables:<\/p>\n<\/blockquote>\n<pre><code>LOCK TABLES a WRITE;\nINSERT INTO a VALUES (1,23),(2,34),(4,33);\nINSERT INTO a VALUES (8,26),(6,29);\n\/* ... *\/\nUNLOCK TABLES;\n<\/code><\/pre>\n<blockquote>\n<p>This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally, there would be as many index buffer flushes as there are INSERT statements. Explicit locking statements are not needed if you can insert all rows with a single INSERT.<\/p>\n<\/blockquote>\n<p>Read through the entire page for details<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Which Insert query run faster and accurate? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] TL;TR The second query will be faster. Why? Read below&#8230; Basically, a query is executed in various steps: Connecting: Both versions of your code have to do this Sending query to server: Applies to both versions, only the second version sends only one query Parsing query: Same as above, both versions need the queries &#8230; <a title=\"[Solved] Which Insert query run faster and accurate?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/\" aria-label=\"More on [Solved] Which Insert query run faster and accurate?\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[1366,340,339],"class_list":["post-26700","post","type-post","status-publish","format-standard","hentry","category-solved","tag-insert","tag-mysql","tag-php"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] Which Insert query run faster and accurate? - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Which Insert query run faster and accurate? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] TL;TR The second query will be faster. Why? Read below&#8230; Basically, a query is executed in various steps: Connecting: Both versions of your code have to do this Sending query to server: Applies to both versions, only the second version sends only one query Parsing query: Same as above, both versions need the queries ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-19T07:00:55+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Which Insert query run faster and accurate?\",\"datePublished\":\"2022-12-19T07:00:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/\"},\"wordCount\":403,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"insert\",\"mysql\",\"php\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/\",\"name\":\"[Solved] Which Insert query run faster and accurate? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-12-19T07:00:55+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-which-insert-query-run-faster-and-accurate\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Which Insert query run faster and accurate?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Which Insert query run faster and accurate? - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Which Insert query run faster and accurate? - JassWeb","og_description":"[ad_1] TL;TR The second query will be faster. Why? Read below&#8230; Basically, a query is executed in various steps: Connecting: Both versions of your code have to do this Sending query to server: Applies to both versions, only the second version sends only one query Parsing query: Same as above, both versions need the queries ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/","og_site_name":"JassWeb","article_published_time":"2022-12-19T07:00:55+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Which Insert query run faster and accurate?","datePublished":"2022-12-19T07:00:55+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/"},"wordCount":403,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["insert","mysql","php"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/","url":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/","name":"[Solved] Which Insert query run faster and accurate? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-19T07:00:55+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-which-insert-query-run-faster-and-accurate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Which Insert query run faster and accurate?"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/26700","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=26700"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/26700\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=26700"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=26700"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=26700"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}