List:General Discussion« Previous MessageNext Message »
From:Bryan Heitman Date:December 23 2004 10:07pm
Subject:Re: scanning 2 rows slow index fast 26GB MyISAM
View as plain text  
Hi Donny,

Remember that the Date field is also indexed:
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`);

Whether it is a SELECT COUNT(*) or a SELECT locationid, it still must 
evaluate the Date field, in both cases it should and does (according to 
explain) use the key: myKey w/o a table-scan required.

The select as you suggest w/o the date key is the same speed only it returns 
thousands of results, this query would make sense why it is slow to retrieve 
data.  However, my previous query which only returns 2 results should be 
fast and not slow.

You are right that SELECT COUNT(*) is a different query.  My point is the 
only difference is a SELECT locationid must retrieve the results as 
locationid is not in the index.  It should not however take 5 mins to return 
2 rows from the table.

Bryan
----- Original Message ----- 
From: "Donny Simonton" <donny@stripped>
To: "'Bryan Heitman'" <bryan@stripped>; "'Sergio Salvi'" 
<list_mysql@stripped>
Cc: <mysql@stripped>
Sent: Thursday, December 23, 2004 2:49 PM
Subject: RE: scanning 2 rows slow index fast 26GB MyISAM


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

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