Re: Extreme slow table with longblob fields

[gerald_clark]

Kiszely Ákos wrote:

Hello list,

I have a MySQL 3.23.49 under Debian Linux with this table:

----------
CREATE TABLE `fajl` (
 `fajl_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
 `fajl` LONGBLOB,
 `letoltesek_szama` INTEGER(11) NOT NULL DEFAULT '0',
 `filename` VARCHAR(80) DEFAULT NULL,
 `fajl_tipus` VARCHAR(20) DEFAULT NULL,
 `fajl_meret` INTEGER(11) DEFAULT NULL,
 `feltolto_id` VARCHAR(15) DEFAULT NULL,
 `feltoltes_datuma` DATETIME DEFAULT NULL,
 `csoport_id` INTEGER(11) DEFAULT NULL,
 `kiterjesztes` VARCHAR(80) NOT NULL,
 PRIMARY KEY (`fajl_id`),
 KEY `NormalIndex` (`fajl_id`, `filename`),
 KEY `fajl_tipus` (`fajl_tipus`)
);
----------
This table contains 299 record, and size of the table is 161MB.

Some simple selects are very fast:

SELECT fajl_id FROM fajl     -> 0.04 sec

Read from key.

SELECT letoltesek_szama FROM fajl     -> 0.42 sec

Beats me.

SELECT filename FROM  fajl -> 0.22sec

Read from key.

SELECT fajl_tipus FROM  fajl -> 0.06 sec

Read from key.

SELECT  filename, fajl_id FROM  fajl -> 0.04sec !!!

Read from key.

BUT! :

These all require a full table scan, and with that longblob in there it 
is going to
take some time.

SELECT   kiterjesztes FROM  fajl -> 12.43 !!!

SELECT  fajl_id, fajl.letoltesek_szama FROM fajl  -> 12.02sec
SELECT  fajl_tipus, letoltesek_szama FROM  fajl -> 15.62
SELECT  fajl_tipus, fajl_meret FROM  fajl  ->14.74
SELECT  fajl_id, feltolto_id FROM  fajl -> 12.85 sec
SELECT  fajl_id, feltoltes_datuma FROM  fajl -> 12.40sec
SELECT   fajl_id, kiterjesztes FROM  fajl -> 12.13
SELECT  filename, letoltesek_szama FROM  fajl  -> 13.62
SELECT  filename, fajl_tipus FROM  fajl -> 12.21
This is extreme slow :-(. I don't know why...
I tried analyze fajl; but nothing changed.
I have an another MySQL on Windows 2000 with same problem. 300 record,
150MB, and very slow simple selects.
Is this an MySQL bug? What can i do, to solve this problem ?

Cheers,
Akos Kiszely


###########################################
Ezt a levelet az F-Secure Anti-Virus for Internet Mail ellenőrizte.
Miskolci Egyetem Számítóközpont
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]