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/'
1) Do you have cardinalities for the following columns?
accountid
wordid
position
date
2) Do you have the EXPLAIN for the SELECT query you posted?
