List:General Discussion« Previous MessageNext Message »
From:Bryan Heitman Date:December 23 2004 3:38pm
Subject:Re: scanning 2 rows slow index fast 26GB MyISAM
View as plain text  
Thanks Leo,

However, remember that the key returns quickly on index-only queries, so the
conditions in the WHERE are not my delay at this time.  I want to know why 
it takes 5
mins to scan 2 rows from the MYD

Bryan

----- Original Message ----- 
From: "Bryan Heitman" <bryanh@stripped>
To: "leo" <okyejr@stripped>
Sent: Thursday, December 23, 2004 9:37 AM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM


> Thanks Leo,
>
> However, remember that the key returns quickly on index-only queries, so 
> the conditions in the WHERE are not the problem.  I want to know why it 
> takes 5 mins to scan 2 rows from the MYD
>
> Bryan
> ----- Original Message ----- 
> From: "leo" <okyejr@stripped>
> To: "Bryan Heitman" <bryan@stripped>
> Sent: Thursday, December 23, 2004 2:00 AM
> Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
>
>
> HI,Bryan Heitman,
>
> You may change there column `date` to type int,and fill in it with 
> UNIX_TMIESTAMP format value,and alter the key `myKey` on 
> (`AccountID`,`wordid`,`position`,`Date`) if you often use statement like 
> "where accountid = xx and wordid = xx and position = 'xx' and date > 
> now() - interval 10 day".
>
>
> ======= 2004-12-22 22:17:00 您在来信中写道:=======
>
>>I am experiencing extreme slowness performing a query in which 2 rows are
>>returned hanging in the "sending data" status.
>>
>>Performing an index only query such as SELECT COUNT(*) is extremely quick 
>>so
>>I know the only extra step is retrieving the data from the MYD.
>>
>>I am looking for thoughts on why this is slow and what can be done to 
>>speed
>>it up.  I find it unusual why it would take this long to simply grab 2 
>>rows
>>from the MYD.  vmstat reports high reads and strace confirms pread()'s on
>>the MYD file.
>>
>>The only abnormality is my  table size MYD is 26 gig and my MYI is about 
>>30
>>gig.
>>
>>Test system details, tests were performed with no load.
>>System: Redhat Linux 2.4.28
>>Mysql: tested on versions 4.0.22 and latest 4.1 tree
>>IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at
>>beginning of disk)
>>IDE (WD 2500 JB 8 mb buff) disk2 used for MYI
>>dual xeon 2.4ghz
>>1gb ddr266 mem
>>
>>Here are query tests & detail below:
>>
>>mysql> select count(*) from matrix where accountid = 11 and wordid = 72 
>>and
>>position = 'Body' and date > now() - interval 10 day;
>>+----------+
>>| count(*) |
>>+----------+
>>|        2 |
>>+----------+
>>1 row in set (0.06 sec)
>>
>>
>>mysql> select locationid from matrix where accountid = 11 and wordid = 72
>>and position = 'Body' and date > now() - interval 10 day;
>>+------------+
>>| locationid |
>>+------------+
>>|      47932 |
>>|      29571 |
>>+------------+
>>2 rows in set (5 min 35.93 sec)
>>
>>mysql> explain select locationid from matrix where accountid = 11 and 
>>wordid
>>= 71 and position = 'Body' and date > now() - interval 10 day;
>>+--------+------+---------------+-------+---------+-------------------+-------+-------------+
>>| table  | type | possible_keys | key   | key_len | ref               | 
>>rows
>>| Extra       |
>>+--------+------+---------------+-------+---------+-------------------+-------+-------------+
>>| matrix | ref  | myKey         | myKey |       9 | const,const,const |
>>56909 | Using where |
>>+--------+------+---------------+-------+---------+-------------------+-------+-------------+
>>
>>CREATE TABLE `location` (
>>  `LocationID` int(11) unsigned NOT NULL auto_increment,
>>  `ImapUID` int(11) unsigned NOT NULL default '0',
>>  `AccountID` int(11) unsigned NOT NULL default '0',
>>  `Date` timestamp(19) NOT NULL,
>>  `FromWho` tinyblob,
>>  `Subject` tinyblob,
>>  `SentTo` tinyblob,
>>  `mailbox` varchar(255) default NULL,
>>  `body` longblob,
>>  PRIMARY KEY  (`LocationID`),
>>  KEY `myKey` (`LocationID`,`AccountID`,`Date`)
>>) TYPE=MyISAM MAX_ROWS=1000000000 AVG_ROW_LENGTH=300 DATA
>>DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/'
>>
>>
>>
>>
>>-- 
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>>.
>
> = = = = = = = = = = = = = = = = = = = =
>
> 给你的祝福,要让你招架不住!
>         致
> 礼!
>
>
>         leo
>         okyejr@stripped
>           2004-12-23
>
>
>
>
>
> 

Thread
scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
  • Re: scanning 2 rows slow index fast 26GB MyISAMSergio Salvi23 Dec
    • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
      • Re: scanning 2 rows slow index fast 26GB MyISAMSergio Salvi23 Dec
        • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
          • Re: scanning 2 rows slow index fast 26GB MyISAMJeff Smelser23 Dec
          • RE: scanning 2 rows slow index fast 26GB MyISAMDonny Simonton23 Dec
            • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
  • Re: scanning 2 rows slow index fast 26GB MyISAMBenoit St-Jean23 Dec
Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec