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