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 > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
