Bryan, Select count(*) is basically a different query then select locationid or any of your fields. I have tables with way more than a billion rows of information, I have some in innodb and some in myisam, and neither of them when heavily loaded will take as long as yours is taking.
I recommend that you try this: Run and Explain: select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body'; Who many results do you get? Then run and explain: select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date > now() - interval 10 day; How many results to you get on this query? I am betting the problem is that you only have results in the past 10 days and nothing before that with accounted =11, worded =72 and position = Body. Which would then do a scan on the date, since it doesn't have anything before that. Just a theory. Donny > -----Original Message----- > From: Bryan Heitman [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 23, 2004 2:02 PM > To: Sergio Salvi > Cc: mysql@lists.mysql.com > Subject: Re: scanning 2 rows slow index fast 26GB MyISAM > > Hi Sergio, > > All of your suggestions deal with key optimization, I do not believe I > have > a key issue here. Remember that select count(*), an index-only query > returns in .06 seconds which is very quick. The real question, is why > does > it take 5 mins to retrieve the row data for these 2 rows that the index > retrieved so quickly. Why the delay and why the heavy read activity on > the > MYD file. > > That to me does not make a lot of sense on the time it takes, does MyISAM > not handle large MYD files w/ a billion rows that well where I should > split > my data across many tables instead? I have certainly not ran across this > issue before, but this is the first time I have a table with a billion > rows. > > mysql> show index from matrix; > +--------+------------+----------+--------------+-------------+----------- > +-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation > | > Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +--------+------------+----------+--------------+-------------+----------- > +-------------+----------+--------+------+------------+---------+ > | matrix | 1 | myKey | 1 | AccountID | A > | > NULL | NULL | NULL | | BTREE | | > | matrix | 1 | myKey | 2 | WordID | A > | > NULL | NULL | NULL | | BTREE | | > | matrix | 1 | myKey | 3 | Position | A > | > NULL | NULL | NULL | | BTREE | | > | matrix | 1 | myKey | 4 | date | A > | > NULL | NULL | NULL | | BTREE | | > | matrix | 1 | myKey2 | 1 | LocationID | A > | > NULL | NULL | NULL | | BTREE | | > +--------+------------+----------+--------------+-------------+----------- > +-------------+----------+--------+------+------------+---------+ > 5 rows in set (0.00 sec) > > ----- Original Message ----- > From: "Sergio Salvi" <[EMAIL PROTECTED]> > To: "Bryan Heitman" <[EMAIL PROTECTED]> > Cc: <mysql@lists.mysql.com> > Sent: Thursday, December 23, 2004 12:01 PM > Subject: Re: scanning 2 rows slow index fast 26GB MyISAM > > > > > > On Thu, 23 Dec 2004, Bryan Heitman wrote: > > > >> My mistake! Here you go: > > > > Ok, no prob :) > > > >> > >> 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/' > >> > > > > Oops, I forgot to ask you to send the output of "show index from > matrix". > > But your index "myKey" looks goods, you could try changing the order of > > the fields in your key. Try creating a index with your fields ordered by > > the "Cardinality" value from the "show index from matrix" output > > (in asceding order). > > > > Also, what happens if you don't specify the "date" value in your query? > > Check the time it takes and the explain output. > > > > Another thing I would suggest is to create (or replace) your index, > > trying all (or almost all) of the possible combinations regarding the > > order of the keys in your index. It helped me in some situations, and > > sometimes it's better for me to keep two indices with the same keys but > > different order, because of my different selects. > > > > Hope that helps! > > > > []s, > > Sergio > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
