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:bryan@stripped]
> Sent: Thursday, December 23, 2004 2:02 PM
> To: Sergio Salvi
> Cc: mysql@stripped
> 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" <list_mysql@stripped>
> To: "Bryan Heitman" <bryan@stripped>
> Cc: <mysql@stripped>
> 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/mysql?unsub=1
