List:General Discussion« Previous MessageNext Message »
From:Sergio Salvi Date:December 23 2004 4:08pm
Subject:Re: scanning 2 rows slow index fast 26GB MyISAM
View as plain text  
Bryan,

Can you send the output of "show create table matrix"? You've just sent 
the "location" table output, but your select command refers to a table 
called "matrix".

[]s,
Sergio.

On Wed, 22 Dec 2004, Bryan Heitman wrote:

> 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/'
> 
> 
> 
> Best regards,
> 
> Bryan Heitman
> FuseMail Team 
> 
> 
> 

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