Re: Similar simple query slow down dramatically, by just select one more field, why?

[Oscar Yen]

N¬™ë,j°jËkj{zºÞw­…«k‰©oz»"¢z
‰¦ºx†j×­˜úèThanks for reply.

----- Original Message ----- 
From: "Ludwig Pummer" <[EMAIL PROTECTED]>
To: "Oscar Yen" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, February 19, 2004 8:39 AM
Subject: Re: Similar simple query slow down dramatically, by just select one more 
field, why?


> *This message was transferred with a trial version of CommuniGate(tm) Pro*
> Oscar Yen wrote:
> > create table a (
> >   imgid int not null,
> >   parent int,
> >   imgtype char(3),
> >   img longtext,
> >   primary key (imgid),
> >   key searchkey (parent, imgid)
> > ) type = innodb;
> > 
> 
> > T1) select imgid, parent from a where parent = 10;
> >    returns 3357 rows in 0.08 sec.
> > T2) select imgid, parent, imgtype from a where parent = 10;
> >    return 3357 rows in 8.32 sec.!!!
> > 
> 
> > 
> > T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in 
> > almost same speed.
> > 
> > Anybody can explain my questions:
> > 
> > Q1- What cause the slow down, T2 vs T1 and T4 vs T3?
> > Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance 
> > of T6 and T4?
> > 
> > Best Reguards.
> 
> Q1:
> It's explained in the MySQL Documentation under Optimization - How MySQL 
> Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay 
> close attention to the 5th bullet. This explains the behavior you see 
> from T7.
Yes, after alter index, the mysql will use index to speedup search.....
But, what caused the mysql refuse to use index searchkey in question T2/T4, 
even if you explicit specify the index you want by re-write the sql like following?

T2) select imgid, parent, imgtype from a force index(searchkey) where parent = 10;

It is clear that non-indexed column imgtype does not contained in where clause!!!, 
Is there any method that I can "PERSUADE" mysql to use index searchkey?

> Q2:
> I have no answer for you there, Sorry.

That's OK, let's change the expression of "poor handle": 

Am I right to assume mysql will store BLOB data along with other columns, when ever 
mysql need to fetch rows, it will read "WHOLE" row, including BLOB data from the disk, 
even if the BLOB data will NOT be used during query? 

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