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
>
>
>
