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