Why would a database insert take >40 seconds?

Post #160,497 by drewk 6/19/04 3:14:14 AM Reply	Why would a database insert take >40 seconds? I'm inserting blobs into a mysql table. No index on the blobs. ~300k-400k. Only about 12 rows in the table. And a single insert takes more than 40 seconds. This doesn't seem right. Is there something I'm missing or is this normal? Are there any tricks to speed it up, like breaking it into chunks and appending it in multiple update queries? === Implicitly condoning stupidity since 2001.
Post #160,524 by Meerkat 6/19/04 11:44:39 AM Reply	No idea, just a wild guess Do subsequent inserts take even longer? Maybe it's doing a full table scan for some reason - but even then that shouldn't take 40 seconds. But the only thing I know about MySQL is how to spell it (and I may even have the capitalisation all wrong) so who knows. Does it have Oracle-esque 'Explain Plan' functionality that may give a clue? Two out of three people wonder where the other one is.
Post #160,529 by drewk 6/19/04 1:59:38 PM Reply	Full table scan is faster I already tested doing a search on the (non-indexed) blob column the data is in. (Remember there are fewer than 40 rows so far.) Even that search takes less time than the insert, so it's not a scan. === Implicitly condoning stupidity since 2001.
Post #160,541 by ben_tilly 6/19/04 3:39:54 PM Reply	Are there indexes on other columns? I'm going to take a WAG as to how it could be that slow. I have no idea whether this is what is happening, but it is a combination that resembles programming decisions that I've seen that makes your performance at least physically possible. In some databases, some types of indexes force data to physically be ordered according to that field. If the blob is being stored with the table row (most databases don't, but MySQL might) this would mean that editing a row in the middle involves rewriting the data for the whole table. Even worse, if MySQL is detecting the need for the rewrite every extra page or so, then it would have to do this rewrite many times for a single insert. I'd suggest trying to create some simple tables moving towards the full definition of yours, and see if you can find out what combination of circumstances triggers the performance problem. Then think about how to lay out your data to avoid the issue. (Possibly break the blobs out into their own table and then replace the original table by a view that does a join.) Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #160,749 by drewk 6/21/04 10:10:54 AM Reply	Here's the table CREATE TABLE `appraisal_files` (\n `idnum` bigint(20) unsigned NOT NULL auto_increment,\n `time_stamp` timestamp(14) NOT NULL,\n `reference_number` bigint(20) unsigned NOT NULL default '0',\n `source_filename` varchar(128) NOT NULL default '',\n `source_extension` varchar(5) NOT NULL default 'PDF',\n `source_data` longblob NOT NULL,\n PRIMARY KEY (`idnum`),\n KEY `time_stamp` (`time_stamp`),\n KEY `reference_number` (`reference_number`)\n) TYPE=MyISAM This already is the separate table for the blobs. === Implicitly condoning stupidity since 2001.
Post #160,750 by admin 6/21/04 10:20:35 AM Reply	What are the indexes though? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #160,761 by drewk 6/21/04 10:41:58 AM Reply	Called KEY in mysql PRIMARY KEY (`idnum`),\n KEY `time_stamp` (`time_stamp`),\n KEY `reference_number` (`reference_number`) === Implicitly condoning stupidity since 2001.
Post #160,775 by admin 6/21/04 11:00:21 AM Reply	Oh, heh. Never mind. Seems silly for them to invent their own syntax... Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."

Welcome to IWETHEY!