Databases
StackOverflow2013
Postsdbo
POHow to make multiple slightly different inserts fast from PHP
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to make multiple slightly different inserts fast from PHP
    text
    copied!<p>I'm doing around 600 <code>INSERT ON DUPLICATE KEY UPDATE</code> queries where each insert can have a different set of columns.</p> <p>The way i'm doing it now is just a foreach loop with a <code>mysql_query()</code>. It's really slow, the php script stops due to maximum execution time of 30s.</p> <p>I cant use <code>INSERT INTO table (columns) VALUES (values 1), (values 2), ..., (values n)</code> because each insert must be able to have a different set of columns.</p> <p>I also looked at prepared queries but from what i saw it seems like that won't work with different column sets either.</p> <p>I'm quite a novice to databases and MYSQL. I'm just hacking together something as a weekend project, and right now i just want to get this last piece of the project to work. I'm sorry about not doing this the proper way. (sorry about using deprecated php mysql functions too.) Maybe i should go to bed and rewrite this later. What would be the proper way to do it?</p> <p>EDIT: here is some info on the table type</p> <pre> -- phpMyAdmin SQL Dump -- version 3.5.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jul 22, 2012 at 09:59 PM -- Server version: 5.5.24-log -- PHP Version: 5.3.13 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; -- -- Database: `trailerbay` -- -- -------------------------------------------------------- -- -- Table structure for table `movies` -- CREATE TABLE IF NOT EXISTS `movies` ( `id` int(11) NOT NULL AUTO_INCREMENT, `imdb` int(7) NOT NULL, `title` text COLLATE utf8_unicode_ci, `releasedate` date DEFAULT NULL, `enlistdate` datetime NOT NULL, `runtime` time DEFAULT NULL, `trailer` text COLLATE utf8_unicode_ci NOT NULL, `plot` text COLLATE utf8_unicode_ci, `cover` text COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `imdb` (`imdb`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1145 ; </pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload