N??j??j{ z恨w???oz??
???篆??i, all
I have a mysql server (dual P4 2.0G, 1G MEM, RH8.0, Mysql 4.0.12),
There are 2 tables defined as follow:
create table a (
imgid int not null,
parent int,
imgtype char(3),
img longtext,
primary key (imgid),
key (parent, imgid)
) type = innodb;
contains about 11000 rows, about 800M in size;
create table b (
docid char(2) not null,
dockey varchar(60) not null,
owner varchar(8),
data longtext,
primary key (docid, dockey)
key ind_docid (docid),
key ind_dockey (dockey)
) type = innodb;
contains 30000+ rows, about 2.8G in size.
SQL tested:
A1) select imgid, parent from a where parent = 10;
returns 3357 rows in 0.08 sec.
A2) select imgid, parent, imgtype from a where parent = 10;
return 3357 rows in 8.32 sec.!!!
A3) select imgid, parent from a where parent = 10 order by imgid;
returns 3357 rows in 0.1 sec
A4) select imgid, parent, imgtype from a where parent = 10 order by imgid;
returns 3357 rows in 25.88 sec!!!
A5) create table za select imgid, parent, imgtype from a;
10102 rows in 1.71 sec.
A6) select imgid, parent, imgtype from za where parent = 10 order by imgid;
3357 rows in 0.02 sec.
B1) select docid, dockey from b where dockey = 'MR';
27786 rows in 0.15sec;
B2) select docid, dockey, owner from b where dockey='MR';
27786 rows in 0.16sec;
B3) select distinct docid from b where dockey = 'MR';
3426 rows in 85.47sec;
B4) create table zb select docid, dockey from b;
30924 rows in 2.2 sec;
B5) select distinct docid from zb where dockey = 'MR';
3426 rows in 0.24 sec.
Can anybody answer following questions, reguarding the listed test results:
Q-1. What cause the performance downgrade by adding single imgtype field to select?
(A1 vs A2, A3 vs A4)
Q-2. Why no noticable performace downgrade on similar selects on table B? (B1 vs B2)?
Q-3. How to explain the performance differences,just because single BLOB field?(A4 vs
A6, and B3 vs B5)?
comment: Tests also performed after I change the table type to mysql native for both
table a and b, the result is similar.
Thanks you for your attention.
Oscar Yen.
