My mistake! Here you go:
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=1000000000 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
----- Original Message -----
From: "Sergio Salvi" <list_mysql@stripped>
To: "Bryan Heitman" <bryan@stripped>
Cc: <mysql@stripped>
Sent: Thursday, December 23, 2004 10:08 AM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
> 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/'
>>
>>
>>
>>
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>
