MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow subquery
Posted by: Matthias Horn
Date: December 30, 2005 12:43PM

Hello Igor, hello Michael,

the actual query is:
Delete from "UserFields" where not exists (select "idThumb" from "Thumbnail" where "idThumb" = "idThumbUDF")

It is intended to delete orphaned records with user fields and runs on each move of a thumb.
The databases officially supported for ThumbsPlus (esp. Jet Engine and MS SQL) seem to handle such queries very well.

I'm running MySQL 5.0.17 on a (a bit outdated) Intel Debian machine. Together with ThumbsPlus it does an impressing good job and gives very short response times for all actions besides the ones involving the query mentioned above.

The tables are defined as follows:
CREATE TABLE "UserFields" (
"idThumbUDF" INT(11) NOT NULL,
# about 130 columns of the type
"uf_XYZ" LONGTEXT,
....
PRIMARY KEY ("idThumbUDF")
) TYPE=InnoDB;

CREATE TABLE "Thumbnail" (
"idThumb" INT(11) NOT NULL AUTO_INCREMENT,
"idPath" INT(11) NOT NULL ,
"idFiletype" INT(11) DEFAULT NULL,
"file_time" INT(11) DEFAULT NULL,
"thumbnail_time" INT(11) DEFAULT NULL,
"filesize" INT(11) DEFAULT NULL,
"checksum" INT(11) DEFAULT NULL,
"width" INT(11) DEFAULT NULL,
"height" INT(11) DEFAULT NULL,
"horiz_res" INT(11) DEFAULT NULL,
"vert_res" INT(11) DEFAULT NULL,
"colortype" SMALLINT(6) DEFAULT NULL,
"colordepth" SMALLINT(6) DEFAULT NULL,
"gamma" SMALLINT(6) DEFAULT NULL,
"thumbnail_width" SMALLINT(6) NOT NULL,
"thumbnail_height" SMALLINT(6) NOT NULL,
"thumbnail_type" INT(11) DEFAULT NULL,
"thumbnail_size" INT(11) DEFAULT NULL,
"name" VARCHAR(255) DEFAULT NULL,
"Thumbnail" LONGBLOB,
"annotation" TEXT,
"metric1" TINYBLOB,
"metric2" TINYBLOB,
"metric3" TINYBLOB,
PRIMARY KEY ("idThumb"),
INDEX ("idPath"),
INDEX ("name"),
INDEX ("idPath","name")
) TYPE=InnoDB;

In my database both tables contain about 14000 rows.
The query takes about 100 seconds and does not delete any row.

Since the people here are not able to change the query, a solution which requires only some reconfiguring of the database (configuration, engine, indexes, triggers) itself would be very helpful.

Regards,
Matthias

Options: ReplyQuote


Subject
Views
Written By
Posted
6468
December 01, 2005 03:33AM
2840
December 07, 2005 08:18AM
2650
December 09, 2005 02:32PM
2579
December 19, 2005 11:58PM
Re: slow subquery
3098
December 30, 2005 12:43PM
3331
December 30, 2005 02:25PM
2544
February 22, 2006 01:06PM
5146
February 23, 2006 08:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.