At 08:10 AM 7/21/2006, Michael Sutter wrote:
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(8) | 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?
Try moving the LongBlob to a table by itself with a rcd_id column that
matches the one in your other table. Then do a join on the two tables when
you need to access KeyFile. The problem may be KeyFile is very large and
that results in a lot of unecessary disk i/o.
Mike
Thanks and Regards
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
