List:General Discussion« Previous MessageNext Message »
From:Bryan Heitman Date:December 23 2004 8:01pm
Subject:Re: scanning 2 rows slow index fast 26GB MyISAM
View as plain text  
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
>
>
> 

Thread
scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
  • Re: scanning 2 rows slow index fast 26GB MyISAMSergio Salvi23 Dec
    • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
      • Re: scanning 2 rows slow index fast 26GB MyISAMSergio Salvi23 Dec
        • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
          • Re: scanning 2 rows slow index fast 26GB MyISAMJeff Smelser23 Dec
          • RE: scanning 2 rows slow index fast 26GB MyISAMDonny Simonton23 Dec
            • Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec
  • Re: scanning 2 rows slow index fast 26GB MyISAMBenoit St-Jean23 Dec
Re: scanning 2 rows slow index fast 26GB MyISAMBryan Heitman23 Dec