Very slow query

Michael Sutter · *nix forums beginner Joined: 23 Mar 2006 Posts: 5

Hello everbody,

I have a table in my MySQL 5.0.22 Server with this columns.

+--------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------------------+-------+
| DATE | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| IMAGE | longblob | NO | | NULL | |
| IMAGETYPE | varchar( Cool

| NO | MUL | NULL | |
| KEYFILE | longblob | NO | | NULL | |
| SEARCHENGINE | varchar(64) | NO | MUL | NULL | |
| SEARCHTERM | varchar(256) | NO | MUL | NULL | |
| SIZE | int(11) | NO | MUL | 0 | |
| URL | varchar(512) | NO | | NULL | |
| NAME | varchar(256) | NO | MUL | NULL | |
+--------------+--------------+------+-----+---------------------+-------+

When I insert several hundert rows with data (all columns, but without
the keyfile column) and query the database with this string:

SELECT date, imagetype, searchengine, searchterm, size, url, name FROM
digiforensic where searchterm='...'

it goes very fast (less than one second) and the right index for
searchterm is used. This even works when I shutdown and restart the server.

The problem is, that the keyfiles are calculated after the datasets are
inserted into the database. After calculation they are inserted with an
update
of the corresponding row. After that the query of the datasets takes a
lot of time - more than 2 minutes. I checked that the right index is
used with the EXPLAIN expression.

Did anybody know why this happens or what my error is?

Thanks and Regards
Michael

Google