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