MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Oscar Yen Date:February 12 2004 2:56am
Subject:Strange slow queries
View as plain text  
NHi, 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.
Thread
Strange slow queriesOscar Yen12 Feb